DECLARE @id_list VARCHAR(MAX) = '1234,23,56,576,1231,567,122,87876,57553,1216';
DECLARE @table TABLE ( id VARCHAR(50) );
DECLARE @x INT = 0;
DECLARE @firstcomma INT = 0;
DECLARE @nextcomma INT = 0;

SET @x = LEN(@id_list) - LEN(REPLACE(@id_list, ',', '')) + 1; -- number of ids in id_list

WHILE @x > 0
    BEGIN
        SET @nextcomma = CASE WHEN CHARINDEX(',', @id_list, @firstcomma + 1) = 0
                              THEN LEN(@id_list) + 1
                              ELSE CHARINDEX(',', @id_list, @firstcomma + 1)
                         END;
        INSERT  INTO @table
        VALUES  ( SUBSTRING(@id_list, @firstcomma + 1, (@nextcomma - @firstcomma) - 1) );
        SET @firstcomma = CHARINDEX(',', @id_list, @firstcomma + 1);
        SET @x = @x - 1;
    END;

SELECT  *
FROM    @table;

以下工作在sql server 2008

select *, ROW_NUMBER() OVER(order by items) as row# 
from 
( select 134 myColumn1, 34 myColumn2, 'd,c,k,e,f,g,h,a' comaSeperatedColumn) myTable
    cross apply 
SPLIT (rtrim(comaSeperatedColumn), ',') splitedTable -- gives 'items'  column 

将得到所有的笛卡尔积与原表列加上“项目”的分割表。

select t.OtherID,x.Kod
    from testData t
    cross apply (select Code from dbo.Split(t.Data,',') ) x

可以使用以下函数提取数据

CREATE FUNCTION [dbo].[SplitString]
(    
    @RowData NVARCHAR(MAX),
    @Delimeter NVARCHAR(MAX)
)
RETURNS @RtnValue TABLE 
(
    ID INT IDENTITY(1,1),
    Data NVARCHAR(MAX)
) 
AS
BEGIN 
    DECLARE @Iterator INT;
    SET @Iterator = 1;

    DECLARE @FoundIndex INT;
    SET @FoundIndex = CHARINDEX(@Delimeter,@RowData);

    WHILE (@FoundIndex>0)
    BEGIN
        INSERT INTO @RtnValue (data)
        SELECT 
            Data = LTRIM(RTRIM(SUBSTRING(@RowData, 1, @FoundIndex - 1)));

        SET @RowData = SUBSTRING(@RowData,
                @FoundIndex + DATALENGTH(@Delimeter) / 2,
                LEN(@RowData));

        SET @Iterator = @Iterator + 1;
        SET @FoundIndex = CHARINDEX(@Delimeter, @RowData);
    END;
    
    INSERT INTO @RtnValue (Data)
    SELECT Data = LTRIM(RTRIM(@RowData));

    RETURN;
END;

当使用这种方法时，您必须确保您的值中没有包含非法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;

你可以使用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