我知道它有很多答案，但我想写我的版本的分裂函数像其他人和像string_split SQL Server 2016本机函数。

create function [dbo].[Split]
(
    @Value nvarchar(max),
    @Delimiter nvarchar(50)
)
returns @tbl table
(
    Seq int primary key identity(1, 1),
    Value nvarchar(max)
)
as begin
    declare @Xml xml = cast('<d>' + replace(@Value, @Delimiter, '</d><d>') + '</d>' as xml);

    insert into @tbl
            (Value)
    select  a.split.value('.', 'nvarchar(max)') as Value
    from    @Xml.nodes('/d') a(split);
    
    return;
end;

Seq列是支持快速连接其他实表或Split函数返回表的主键。 使用XML函数支持大数据(当你有大数据时，循环版本会显著变慢)

这是问题的答案。

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';


select  t.SomeID, t.OtherID, s.Value
from    Testdata t
        cross apply dbo.Split(t.String, ',') s;

--Output
SomeID  OtherID Value
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

加入Split与其他Split

declare @Names nvarchar(max) = 'a,b,c,d';
declare @Codes nvarchar(max) = '10,20,30,40';

select  n.Seq, n.Value Name, c.Value Code
from    dbo.Split(@Names, ',') n
        inner join dbo.Split(@Codes, ',') c on n.Seq = c.Seq;

--Output
Seq Name    Code
1   a       10
2   b       20
3   c       30
4   d       40

分开两次

declare @NationLocSex nvarchar(max) = 'Korea,Seoul,1;Vietnam,Kiengiang,0;China,Xian,0';

with rows as
(
    select  Value
    from    dbo.Split(@NationLocSex, ';')
)
select  rw.Value r, cl.Value c
from    rows rw
        cross apply dbo.Split(rw.Value, ',') cl;

--Output
r                       c
Korea,Seoul,1           Korea
Korea,Seoul,1           Seoul
Korea,Seoul,1           1
Vietnam,Kiengiang,0     Vietnam
Vietnam,Kiengiang,0     Kiengiang
Vietnam,Kiengiang,0     0
China,Xian,0            China
China,Xian,0            Xian
China,Xian,0            0

分割成列

declare @Numbers nvarchar(50) = 'First,Second,Third';

with t as
(
    select  case when Seq = 1 then Value end f1,
            case when Seq = 2 then Value end f2,
            case when Seq = 3 then Value end f3
    from    dbo.Split(@Numbers, ',')
)
select  min(f1) f1, min(f2) f2, min(f3) f3
from    t;

--Output
f1      f2      f3
First   Second  Third

按范围生成行

declare @Ranges nvarchar(50) = '1-2,4-6';

declare @Numbers table (Num int);
insert into @Numbers values (1),(2),(3),(4),(5),(6),(7),(8);

with t as
(
    select  r.Seq, r.Value,
            min(case when ft.Seq = 1 then ft.Value end) ValueFrom,
            min(case when ft.Seq = 2 then ft.Value end) ValueTo
    from    dbo.Split(@Ranges, ',') r
            cross apply dbo.Split(r.Value, '-') ft
    group by r.Seq, r.Value
)
select  t.Seq, t.Value, t.ValueFrom, t.ValueTo, n.Num
from    t
        inner join @Numbers n on n.Num between t.ValueFrom and t.ValueTo;

--Output
Seq Value   ValueFrom   ValueTo Num
1   1-2     1           2       1
1   1-2     1           2       2
2   4-6     4           6       4
2   4-6     4           6       5
2   4-6     4           6       6

截至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;

检查这个

 SELECT A.OtherID,  
     Split.a.value('.', 'VARCHAR(100)') AS Data  
 FROM  
 (
     SELECT OtherID,  
         CAST ('<M>' + REPLACE(Data, ',', '</M><M>') + '</M>' AS XML) AS Data  
     FROM  Table1
 ) AS A CROSS APPLY Data.nodes ('/M') AS Split(a); 

请参考下面的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); 

结果:

颜色

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

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

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;

以下工作在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 

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