How to split long string function in sql server?

[rajraman04112009]

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,15 8,159,160,161,1 62,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.

[Delerna]

[code=sql]
set @PortionSize=le n(@Joins)/3
set @LeftSide=left( @Joins,@Portion Size)
set @Middle=substri ng(@Joins,@Port ionSize+1,@Port ionSize-1)
set @RightSide=righ t(@Joins,@Porti onSize)
[/code]

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.

[rajraman04112009]

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,15 0,151,154,155,1 58,159,160,161, 162,163,165,166 ,167,168,169,17 0,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

[Delerna]

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

[nbiswas]

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