Read comma separated string in Sql server in loop

Question

I have nvarchar data like this:

'20030,20031,20033,20034,20065,20045,20044'

I want to read all this by spliting into array or read one by one in spliting process and do further process.

I have tried this good article but not able to do it.

Answer 1

Try this article uses function to read the comma separated values and return the table

CREATE FUNCTION dbo.Split(@String nvarchar(4000), @Delimiter char(1))
RETURNS @Results TABLE (Items nvarchar(4000))
AS
BEGIN
DECLARE @INDEX INT
DECLARE @SLICE nvarchar(4000)
-- HAVE TO SET TO 1 SO IT DOESNT EQUAL Z
--     ERO FIRST TIME IN LOOP
SELECT @INDEX = 1
WHILE @INDEX !=0
BEGIN
-- GET THE INDEX OF THE FIRST OCCURENCE OF THE SPLIT CHARACTER
SELECT @INDEX = CHARINDEX(@Delimiter,@STRING)
-- NOW PUSH EVERYTHING TO THE LEFT OF IT INTO THE SLICE VARIABLE
IF @INDEX !=0
SELECT @SLICE = LEFT(@STRING,@INDEX - 1)
ELSE
SELECT @SLICE = @STRING
-- PUT THE ITEM INTO THE RESULTS SET
INSERT INTO @Results(Items) VALUES(@SLICE)
-- CHOP THE ITEM REMOVED OFF THE MAIN STRING
SELECT @STRING = RIGHT(@STRING,LEN(@STRING) - @INDEX)
-- BREAK OUT IF WE ARE DONE
IF LEN(@STRING) = 0 BREAK
END
RETURN
END

just call the function from stored procedure or just call function like below:

SELECT items FROM [dbo].[Split] ('20030,20031,20033,20034,20065,20045,20044', ',') 

Answer 2

Not hard at all. It can be best solved using recursion

create table #Testdata2(Data varchar(max))
insert #Testdata2 select '20030,20031,20033,20034,20065,20045,20044'



;with tmp(DataItem, Data) as (
select LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from #Testdata2
union all
select LEFT(Data, CHARINDEX(',',Data+',')-1),
    STUFF(Data, 1, CHARINDEX(',',Data+','), '')
from tmp
where Data > ''
)
select DataItem
from tmp
OPTION (maxrecursion 0)

output :

(select)
 Data
 ----------------
 20030,20031,20033,20034,20065,20045,20044


(query)
DataItem
----------------
20030
20031
20033
20034
20065
20045
20044

Answer 3

Another approach using String Split And Xml

DECLARE @string nvarchar(max)='20030,20031,20033,20034,20065,20045,20044'
DECLARE @Table AS TABLE (String nvarchar(max))
INSERT INTO @Table
SELECT @string

SELECT Row_NUmber ()OVER(ORDER BY (SELECT 1) )AS Seq, Split.a.value('.', 'VARCHAR(1000)') AS String
            FROM  (
                SELECT   CAST('<S>' + REPLACE(String, ',', '</S><S>') + '</S>' AS XML) AS String
                FROM @Table
                ) AS A
            CROSS APPLY String.nodes('/S') AS Split(a) 

The above code can be incorporated in function to return split string

CREATE FUNCTION dbo.udf_Split (
    @String NVARCHAR(4000)
    ,@Delimiter CHAR(1)
    )
RETURNS @Results TABLE (Items NVARCHAR(4000))
AS
BEGIN
    DECLARE @Table AS TABLE (String NVARCHAR(max))

    INSERT INTO @Table
    SELECT @String

    INSERT INTO @Results
    SELECT Split.a.value('.', 'VARCHAR(1000)') AS String
    FROM (
        SELECT CAST('<S>' + REPLACE(String, @Delimiter, '</S><S>') + '</S>' AS XML) AS String
        FROM @Table
        ) AS A
    CROSS APPLY String.nodes('/S') AS Split(a)

    RETURN
END

SELECT ROW_NUMBER()OVER(ORDER BY (SELECT 1))AS Seq,
    * FROM dbo.udf_Split('20030,20031,20033,20034,20065,20045,20044',',')

Result

Seq String
----------
1   20030
2   20031
3   20033
4   20034
5   20065
6   20045
7   20044

Answer 4

If using SQL Server 2017 or later, just use the STRING_SPLIT function.

DECLARE @numbers VARCHAR(MAX) = '7,8,9,10,11'

;WITH cte1 AS (
    SELECT [value] AS Number FROM STRING_SPLIT(@numbers, ',')
)

SELECT ROW_NUMBER() OVER (ORDER BY Number) as Id, Number
FROM cte1;

Results:

Id       Number
-------- -----------
1        7
2        8
3        9
4        10
5        11

(5 rows affected)