使用CTE逗号分隔的字符串 [英] comma separated string using CTE

问题描述

我有一个字符串'1,2,3,4,5,6，'，我想要数组中的结果:

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

1
2
3
4
5

我已经尝试过使用函数，也可以将其转换为xml.

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

我有一个查询:

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;

结果:

2
7
4
5

推荐答案

仅当字符串类似于 '12，34,45,56 ....'，即字符串包含两位数字时，此方法才有效逗号分隔的值

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

这样的函数定义

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

并像这样使用此功能

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

这将给出这样的输出

12个344556677

这篇关于使用CTE逗号分隔的字符串的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！