我知道它有很多答案，但我想写我的版本的分裂函数像其他人和像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

函数

CREATE FUNCTION dbo.SplitToRows (@column varchar(100), @separator varchar(10))
RETURNS @rtnTable TABLE
  (
  ID int identity(1,1),
  ColumnA varchar(max)
  )
 AS
BEGIN
    DECLARE @position int = 0;
    DECLARE @endAt int = 0;
    DECLARE @tempString varchar(100);
    
    set @column = ltrim(rtrim(@column));

    WHILE @position<=len(@column)
    BEGIN       
        set @endAt = CHARINDEX(@separator,@column,@position);
            if(@endAt=0)
            begin
            Insert into @rtnTable(ColumnA) Select substring(@column,@position,len(@column)-@position);
            break;
            end;
        set @tempString = substring(ltrim(rtrim(@column)),@position,@endAt-@position);

        Insert into @rtnTable(ColumnA) select @tempString;
        set @position=@endAt+1;
    END;
    return;
END;

用例

select * from dbo.SplitToRows('T14; p226.0001; eee; 3554;', ';');

或者只是一个有多个结果集的选择

DECLARE @column varchar(max)= '1234; 4748;abcde; 324432';
DECLARE @separator varchar(10) = ';';
DECLARE @position int = 0;
DECLARE @endAt int = 0;
DECLARE @tempString varchar(100);

set @column = ltrim(rtrim(@column));

WHILE @position<=len(@column)
BEGIN       
    set @endAt = CHARINDEX(@separator,@column,@position);
        if(@endAt=0)
        begin
        Select substring(@column,@position,len(@column)-@position);
        break;
        end;
    set @tempString = substring(ltrim(rtrim(@column)),@position,@endAt-@position);

    select @tempString;
    set @position=@endAt+1;
END;

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

结果:

颜色

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

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

谢谢!

检查这个

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