By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,410 Members | 1,626 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,410 IT Pros & Developers. It's quick & easy.

How to split long string function in sql server?

P: 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,'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.

Nov 4 '09 #1
Share this Question
Share on Google+
4 Replies

Expert 100+
P: 1,134
Expand|Select|Wrap|Line Numbers
  1. set @PortionSize=len(@Joins)/3
  2. set @LeftSide=left(@Joins,@PortionSize)
  3. set @Middle=substring(@Joins,@PortionSize+1,@PortionSize-1)
  4. 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.
Nov 5 '09 #2

P: 2
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
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?

Nov 5 '09 #3

Expert 100+
P: 1,134
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
Nov 5 '09 #4

P: 149
Presenting you 2 examples

Solution 1(with recursive CTE)

Expand|Select|Wrap|Line Numbers
  1. declare @str as nvarchar(max)
  2. declare @delimiter as char(1)
  3. set @delimiter = ','
  4. set @str = 'India,USA,Canada,Australia,Bhutan' -- original data
  5. set @str = @delimiter + @str + @delimiter
  7. ;with num_cte as
  8. (     
  9.       select 1 as rn
  10.       union all
  11.       select rn +1 as rn 
  12.       from num_cte 
  13.       where rn <= len(@str)
  14. )
  15. , get_delimiter_pos_cte as
  16.       select      
  17.                   ROW_NUMBER() OVER (ORDER BY rn) as rowid, 
  18.                   rn as delimiterpos            
  19.       from num_cte
  20.       cross apply( select substring(@str,rn,1)  AS chars) splittedchars 
  21.       where chars = @delimiter
  22. )
  24. select substring(@str,a.delimiterpos+1 ,c2.delimiterpos - a.delimiterpos - 1) as Countries
  25. from get_delimiter_pos_cte a
  26. inner join get_delimiter_pos_cte c2 on c2.rowid = a.rowid+1
  27. option(maxrecursion 0)
Solution 2(with XQuery)

Expand|Select|Wrap|Line Numbers
  1. DECLARE @xml as xml,@str as nvarchar(max),@delimiter as varchar(10)
  2. SET @str='India,USA,Canada,Australia,Bhutan'
  3. SET @delimiter =','
  4. SET @xml = cast(('<X>'+replace(@str,@delimiter ,'</X><X>')+'</X>') as xml)
  5. 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
Nov 8 '09 #5

Post your reply

Sign in to post your reply or Sign up for a free account.