将逗号分隔的字符串转换为单独的行

我有一个这样的SQL表:

| SomeID         | OtherID     | Data
+----------------+-------------+-------------------
| abcdef-.....   | cdef123-... | 18,20,22
| abcdef-.....   | 4554a24-... | 17,19
| 987654-.....   | 12324a2-... | 13,19,20

是否有一个查询，我可以执行查询，如SELECT OtherID, SplitData where SomeID = 'abcdef-.......，返回单独的行，就像这样:

| OtherID     | SplitData
+-------------+-------------------
| cdef123-... | 18
| cdef123-... | 20
| cdef123-... | 22
| 4554a24-... | 17
| 4554a24-... | 19

基本上把我的数据在逗号处分割成单独的行?

我知道将逗号分隔的字符串存储到关系数据库中听起来很愚蠢，但消费者应用程序中的正常用例使这种方法非常有用。

我不想在应用程序中进行拆分，因为我需要分页，所以我想在重构整个应用程序之前探索选项。

它是SQL Server 2008(非r2)。

当前回答

当使用这种方法时，您必须确保您的值中没有包含非法XML - user1151923

我总是使用XML方法。确保使用VALID XML。我有两个函数在有效的XML和文本之间转换。(我倾向于去掉回车，因为我通常不需要它们。

CREATE FUNCTION dbo.udf_ConvertTextToXML (@Text varchar(MAX)) 
    RETURNS varchar(MAX)
AS
    BEGIN
        SET @Text = REPLACE(@Text,CHAR(10),'');
        SET @Text = REPLACE(@Text,CHAR(13),'');
        SET @Text = REPLACE(@Text,'<','&lt;');
        SET @Text = REPLACE(@Text,'&','&amp;');
        SET @Text = REPLACE(@Text,'>','&gt;');
        SET @Text = REPLACE(@Text,'''','&apos;');
        SET @Text = REPLACE(@Text,'"','&quot;');
    RETURN @Text;
END;


CREATE FUNCTION dbo.udf_ConvertTextFromXML (@Text VARCHAR(MAX)) 
    RETURNS VARCHAR(max)
AS
    BEGIN
        SET @Text = REPLACE(@Text,'&lt;','<');
        SET @Text = REPLACE(@Text,'&amp;','&');
        SET @Text = REPLACE(@Text,'&gt;','>');
        SET @Text = REPLACE(@Text,'&apos;','''');
        SET @Text = REPLACE(@Text,'&quot;','"');
    RETURN @Text;
END;

2015-03-16 16:40:25

其他回答

请参考下面的TSQL。STRING_SPLIT函数仅在兼容性级别130及更高级别下可用。

TSQL:

DECLARE @stringValue NVARCHAR(400) = 'red,blue,green,yellow,black';
DECLARE @separator CHAR = ',';

SELECT [value]  As Colour
FROM STRING_SPLIT(@stringValue, @separator);

结果:

颜色

红色的蓝色的绿色黄色的黑色的

2018-08-22 02:56:26

很晚了，但是试试这个:

SELECT ColumnID, Column1, value  --Do not change 'value' name. Leave it as it is.
FROM tbl_Sample  
CROSS APPLY STRING_SPLIT(Tags, ','); --'Tags' is the name of column containing comma separated values

所以我们有了这个: tbl_Sample:

ColumnID|   Column1 |   Tags
--------|-----------|-------------
1       |   ABC     |   10,11,12    
2       |   PQR     |   20,21,22

运行此查询后:

ColumnID|   Column1 |   value
--------|-----------|-----------
1       |   ABC     |   10
1       |   ABC     |   11
1       |   ABC     |   12
2       |   PQR     |   20
2       |   PQR     |   21
2       |   PQR     |   22

谢谢!

2019-01-07 06:27:09

截至2016年2月-见talltytable示例-很可能从2014年2月开始超过我的TVF。为子孙后代保留以下原始帖子:

上面的例子中重复的代码太多了。我不喜欢cte和XML的性能。还有一个显式Id，以便特定于订单的消费者可以指定order BY子句。

CREATE FUNCTION dbo.Split
(
    @Line nvarchar(MAX),
    @SplitOn nvarchar(5) = ','
)
RETURNS @RtnValue table
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
    Data nvarchar(100) NOT NULL
)
AS
BEGIN
    IF @Line IS NULL RETURN;

    DECLARE @split_on_len INT = LEN(@SplitOn);
    DECLARE @start_at INT = 1;
    DECLARE @end_at INT;
    DECLARE @data_len INT;

    WHILE 1=1
    BEGIN
        SET @end_at = CHARINDEX(@SplitOn,@Line,@start_at);
        SET @data_len = CASE @end_at WHEN 0 THEN LEN(@Line) ELSE @end_at-@start_at END;
        INSERT INTO @RtnValue (data) VALUES( SUBSTRING(@Line,@start_at,@data_len) );
        IF @end_at = 0 BREAK;
        SET @start_at = @end_at + @split_on_len;
    END;

    RETURN;
END;

2014-01-14 06:20:01

当使用这种方法时，您必须确保您的值中没有包含非法XML - user1151923

我总是使用XML方法。确保使用VALID XML。我有两个函数在有效的XML和文本之间转换。(我倾向于去掉回车，因为我通常不需要它们。

CREATE FUNCTION dbo.udf_ConvertTextToXML (@Text varchar(MAX)) 
    RETURNS varchar(MAX)
AS
    BEGIN
        SET @Text = REPLACE(@Text,CHAR(10),'');
        SET @Text = REPLACE(@Text,CHAR(13),'');
        SET @Text = REPLACE(@Text,'<','&lt;');
        SET @Text = REPLACE(@Text,'&','&amp;');
        SET @Text = REPLACE(@Text,'>','&gt;');
        SET @Text = REPLACE(@Text,'''','&apos;');
        SET @Text = REPLACE(@Text,'"','&quot;');
    RETURN @Text;
END;


CREATE FUNCTION dbo.udf_ConvertTextFromXML (@Text VARCHAR(MAX)) 
    RETURNS VARCHAR(max)
AS
    BEGIN
        SET @Text = REPLACE(@Text,'&lt;','<');
        SET @Text = REPLACE(@Text,'&amp;','&');
        SET @Text = REPLACE(@Text,'&gt;','>');
        SET @Text = REPLACE(@Text,'&apos;','''');
        SET @Text = REPLACE(@Text,'&quot;','"');
    RETURN @Text;
END;

2015-03-16 16:40:25

你可以使用SQL Server中的递归函数:

示例表:

CREATE TABLE Testdata
(
    SomeID INT,
    OtherID INT,
    String VARCHAR(MAX)
);

INSERT Testdata SELECT 1,  9, '18,20,22';
INSERT Testdata SELECT 2,  8, '17,19';
INSERT Testdata SELECT 3,  7, '13,19,20';
INSERT Testdata SELECT 4,  6, '';
INSERT Testdata SELECT 9, 11, '1,2,3,4';

查询

WITH tmp(SomeID, OtherID, DataItem, String) AS
(
    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM Testdata
    UNION all

    SELECT
        SomeID,
        OtherID,
        LEFT(String, CHARINDEX(',', String + ',') - 1),
        STUFF(String, 1, CHARINDEX(',', String + ','), '')
    FROM tmp
    WHERE
        String > ''
)
SELECT
    SomeID,
    OtherID,
    DataItem
FROM tmp
ORDER BY SomeID;
-- OPTION (maxrecursion 0)
-- normally recursion is limited to 100. If you know you have very long
-- strings, uncomment the option

输出

 SomeID | OtherID | DataItem 
--------+---------+----------
 1      | 9       | 18       
 1      | 9       | 20       
 1      | 9       | 22       
 2      | 8       | 17       
 2      | 8       | 19       
 3      | 7       | 13       
 3      | 7       | 19       
 3      | 7       | 20       
 4      | 6       |          
 9      | 11      | 1        
 9      | 11      | 2        
 9      | 11      | 3        
 9      | 11      | 4

2011-03-30 23:18:05

将逗号分隔的字符串转换为单独的行

推荐文章

最新文章

标签