How to split long string function in sql server? | Newbie | | Join Date: Nov 2009
Posts: 2
| | |
I have a variable called @Joins, datatype is nvarchar(Max). Issue is I have a long string as follows:
27,30,35,43,68,144,145,146,150,151,154,155,158,159 ,160,161,162,163,165,166........it's around 50,000 numbers. These are all id's and I need to process something with this id's. I am getting this id's in a variable called @Joins. However, while printing this variable, the data (ids) are truncating and getting only very few even though the data type is nvarchar(Max). How can I split this into two or three variables? Please help.
Thanks
mram.
|  | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 782
| | | re: How to split long string function in sql server?
-
set @PortionSize=len(@Joins)/3
-
set @LeftSide=left(@Joins,@PortionSize)
-
set @Middle=substring(@Joins,@PortionSize+1,@PortionSize-1)
-
set @RightSide=right(@Joins,@PortionSize)
-
You will probably need to make some adjustments so that the same character doesn't get picked up into multipe variables and also that a number doesn't get split into two (use CharIndex() to find commas)
I will let you work that out.
| | Newbie | | Join Date: Nov 2009
Posts: 2
| | | re: How to split long string function in sql server?
How should I avoid the number split into two? Since I am not much into sql server, can you please help me that? I am getting now as follows:
LeftSide -dbo.SplitIds( '27,30,35,43,68,144,145,146,150,151,154,155,158,15 9,160,161,162,163,165,166,167,168,169,170,1
71,
Middle -,1302,1303,1304,1305,1306,1307,1308,1309,1310,1311 ,1313,1314,1315,1316,1317,1318,1319,1320,1321,
How to do with charindex function?
Thanks
| | Expert | | Join Date: Jan 2008 Location: Sydney
Posts: 782
| | | re: How to split long string function in sql server?
CharIndex returns the position within a string of another string.
Check SQL's help documents, it is pretty clear.
You can use that on @middle to find the position of the first comma and use that to adjust @PortionSize. Maybe you need 2 @PortionSize variables. 1 for the left side and 1 for the right side split positions
I am trying to not give you the answer here but rather ideas so you can find the answer for youself.
Think upon it and have a go, if you get stuck, post your code and I, or someone, will help
|  | Newbie | | Join Date: May 2009 Location: India
Posts: 28
| | | re: How to split long string function in sql server?
Presenting you 2 examples Solution 1(with recursive CTE) - declare @str as nvarchar(max)
-
declare @delimiter as char(1)
-
set @delimiter = ','
-
set @str = 'India,USA,Canada,Australia,Bhutan' -- original data
-
set @str = @delimiter + @str + @delimiter
-
-
;with num_cte as
-
(
-
select 1 as rn
-
union all
-
select rn +1 as rn
-
from num_cte
-
where rn <= len(@str)
-
)
-
, get_delimiter_pos_cte as
-
(
-
select
-
ROW_NUMBER() OVER (ORDER BY rn) as rowid,
-
rn as delimiterpos
-
from num_cte
-
cross apply( select substring(@str,rn,1) AS chars) splittedchars
-
where chars = @delimiter
-
)
-
-
select substring(@str,a.delimiterpos+1 ,c2.delimiterpos - a.delimiterpos - 1) as Countries
-
from get_delimiter_pos_cte a
-
inner join get_delimiter_pos_cte c2 on c2.rowid = a.rowid+1
-
option(maxrecursion 0)
Solution 2(with XQuery) - DECLARE @xml as xml,@str as nvarchar(max),@delimiter as varchar(10)
-
SET @str='India,USA,Canada,Australia,Bhutan'
-
SET @delimiter =','
-
SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
-
SELECT N.value('.', 'varchar(10)') as value FROM @xml.nodes('X') as T(N)
This methods will work in SQL SERVER 2005 & above.
Hope this helps
|  |
Similar Microsoft SQL Server bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,223 network members.
|
