comma separated string using CTE

问题:

I have a string '1,2,3,4,5,6,', i want the result in array like :

1
2
3
4
5

I have tried it using function and also done by convertingit to the xml.

I have a query:

with cte1 (str1,str2) AS
(
SELECT SUBSTRING('1,2,3,4,5,6,',1,1) X,
SUBSTRING('1,2,3,4,5,6,',CHARINDEX(',','1,2,3,4,5,6,,') +1,LEN('1,2,3,4,5,6,')-2) Y
UNION all
SELECT SUBSTRING(str2,1,1) X ,SUBSTRING(str2,CHARINDEX(',',str2)+1,LEN(str2)-2) Y
FROM CTE1
WHERE SUBSTRING(str2,CHARINDEX(',',str2)+0,1) <> ' ' )
SELECT str1 FROM CTE1;

which gives the result as expected. but if i am changing the string it gives random reults like :

with cte1 (str1,str2) AS
(
SELECT SUBSTRING('24,78,45,56,',1,1) X,
SUBSTRING('24,78,45,56,',CHARINDEX(',','24,78,45,56,') +1,LEN('24,78,45,56,')-2) Y
UNION all
SELECT SUBSTRING(str2,1,1) X ,SUBSTRING(str2,CHARINDEX(',',str2)+1,LEN(str2)-2) Y
FROM CTE1
WHERE SUBSTRING(str2,CHARINDEX(',',str2)+0,1) <> ' ' )
SELECT str1 FROM CTE1;

result :

2
7
4
5

回答1:

This will work only when string is like '12,34,45,56....'i.e string contains two digit comm separated values

with cte1 (str1,str2) AS
(
 SELECT SUBSTRING('24,78,45,56,',1,2) X,
        SUBSTRING('24,78,45,56,',CHARINDEX(',','24,78,45,56,') +1,LEN('24,78,45,56,')-2) Y
 UNION all
 SELECT SUBSTRING(str2,1,2) X ,SUBSTRING(str2,CHARINDEX(',',str2)+1,LEN(str2)-2) Y
 FROM CTE1
 WHERE SUBSTRING(str2,CHARINDEX(',',str2)+0,2) <> ' ' )
 SELECT str1 FROM CTE1;

You should go with generic solution by creating on user define function which accepts comma separated string and give table value for this string

Function definition like this

CREATE FUNCTION SplitItem( @ItemIDs VARCHAR(MAX))
RETURNS @ItemTable TABLE ( Item VARCHAR(200) )
AS 
BEGIN
    DECLARE @Item VARCHAR(200)
    DECLARE @Index INT

    WHILE LEN(@ItemIDs) <> 0 
        BEGIN
            SET @Index = PATINDEX('%,%', @ItemIDs)
            IF @Index > 0 
                BEGIN
                    SET @Item = SUBSTRING(@ItemIDs, 1, @Index - 1) 
                    SET @ItemIDs = RIGHT(@ItemIDs, LEN(@ItemIDs) - @Index)
                    INSERT  INTO @ItemTable
                    VALUES  ( @Item )
                END
            ELSE 
                BEGIN
                    BREAK
                END
        END
    SET @Item = @ItemIDs 
    INSERT  INTO @ItemTable
    VALUES  ( @Item )

    RETURN
END

And Use this function like this

SELECT Item 
FROM SplitItem('1,2,3,44,55,66,77')

This will gives output like this

1 2 3 44 55 66 77

回答2:

You could use a recursive CTE

Declare @list NVARCHAR(MAX) = '1,2,3,4,5'
DECLARE @length INT = LEN(@list) + 1;

   WITH a AS
   (
       SELECT
           [start] = 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(',', 
                       @List, 1), 0), @length),
           [value] = SUBSTRING(@list, 1, 
                     COALESCE(NULLIF(CHARINDEX(',', 
                       @List, 1), 0), @length) - 1)
       UNION ALL
       SELECT
           [start] = CONVERT(INT, [end]) + 1,
           [end]   = COALESCE(NULLIF(CHARINDEX(',', 
                       @list, [end] + 1), 0), @length),
           [value] = SUBSTRING(@list, [end] + 1, 
                     COALESCE(NULLIF(CHARINDEX(',', 
                       @list, [end] + 1), 0), @length)-[end]-1)
       FROM a
       WHERE [end] < @length
   )
    SELECT [value]
   FROM a
   WHERE LEN([value]) > 0
   OPTION (MAXRECURSION 0);

回答3:

You can do something like this:

DECLARE @string NVARCHAR(MAX) =  '1,2,3,4,5,6,',
        @xml xml

select @xml = cast('<d><q>'+REPLACE(@string,',','</q><q>')+'</q></d>' as xml)

SELECT n.v.value('.','nvarchar(2)')
FROM @xml.nodes('/d/q') AS n(v);

The result:

----
1
2
3
4
5
6


(7 row(s) affected)