CODEWITHSUNDEEP
sql-serversql-server-2005sql-server-2008
Rajasekar Gunasekaran
Rajasekar Gunasekaran

Reputation: 1827

How to split the comma separated string with out using loop in stored procedure

How to split the comma separated string with out using loop

fo example:-

I have the string which has commma separated like 4,3,5,2,5,9

if i like to get the string after 3rd comma without using loop.

Upvotes: 0

Views: 4371

Answers (2)

AlexK
AlexK

Reputation: 9927

DECLARE @str varchar(MAX)='001,002,03,0004,05,6,07'
    ,@separator char(1)=','
    ,@n int = 3;

WITH str_nums ( id, n1, n2 ) 
AS 
( 
SELECT CAST(1 as int) as id, CAST(0 as bigint) as n1, CHARINDEX(@separator, @str+@separator) as n2
UNION ALL
SELECT id + 1, n2 as n1, CHARINDEX(@separator, @str+@separator, n2+1) as n2
FROM str_nums
WHERE n2<len(@str)
) 

SELECT SUBSTRING(@str, n1+1, n2-n1-1) as n_str
FROM str_nums
WHERE id = @n
GO

Upvotes: 1

mwigdahl
mwigdahl

Reputation: 16578

It would help if we knew why you can't use a loop, but you can either use a custom CLR function (which might use a loop behind the scenes) or XQuery:

DECLARE @S varchar(max),
  @Split char(1),
  @X xml

SELECT @S = '1,2,3,4,5',
  @Split = ','

SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

SELECT [Value] = T.c.value('.','varchar(20)')
FROM @X.nodes('/root/s') T(c)

Then to get only the third (or whatever ordinal you want) string you can use a ROW_NUMBER-based solution to pick out the row you care about.

EDIT: Based on the question in the comment, here's a solution for retrieving the third string. You kind of have to fake out the ROW_NUMBER ordering due to the fact that you don't have any data-based ordering to impose. Normally it won't let you ORDER BY a constant, but the CASE statement gets around that. Change the WHERE iq.Ordinal = 3 to whatever you want to retrieve the string you want:

DECLARE @S varchar(max),
  @Split char(1),
  @X xml

SELECT @S = '1,2,3,4,5',
  @Split = ','

SELECT @X = CONVERT(xml,'<root><s>' + REPLACE(@S,@Split,'</s><s>') + '</s></root>')

SELECT TOP 1 Value
FROM
(    
    SELECT [Value] = T.c.value('.','varchar(20)'), 
                ROW_NUMBER() OVER (ORDER BY 
                    CASE 
                        WHEN T.c.value('.', 'varchar(20)') = 0
                        THEN 0 
                        ELSE 0 
                    END ASC) AS Ordinal
    FROM @X.nodes('/root/s') T(c)
) iq
WHERE iq.Ordinal = 3

Upvotes: 1

Related Questions