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: 787
| | | 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: 787
| | | 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
|  | Member | | Join Date: May 2009 Location: India
Posts: 33
| | | 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 |
