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

结果:

颜色

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

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

函数

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;

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

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;

通过创建这个分割字符串的函数([DelimitedSplit])，可以对SELECT执行OUTER APPLY。

CREATE FUNCTION [dbo].[DelimitedSplit]
--===== Define I/O parameters
        (@pString VARCHAR(8000), @pDelimiter CHAR(1))
--WARNING!!! DO NOT USE MAX DATA-TYPES HERE!  IT WILL KILL PERFORMANCE!
RETURNS TABLE WITH SCHEMABINDING AS
 RETURN
--===== "Inline" CTE Driven "Tally Table" produces values from 1 up to 10,000...
     -- enough to cover VARCHAR(8000)
  WITH E1(N) AS (
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
                 SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
                ),                          --10E+1 or 10 rows
       E2(N) AS (SELECT 1 FROM E1 a INNER JOIN E1 b ON b.N = a.N), --10E+2 or 100 rows
       E4(N) AS (SELECT 1 FROM E2 a INNER JOIN E2 b ON b.N = a.N), --10E+4 or 10,000 rows max
 cteTally(N) AS (--==== This provides the "base" CTE and limits the number of rows right up front
                     -- for both a performance gain and prevention of accidental "overruns"
                 SELECT TOP (ISNULL(DATALENGTH(@pString),0)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E4
                ),
cteStart(N1) AS (--==== This returns N+1 (starting position of each "element" just once for each delimiter)
                 SELECT 1 UNION ALL
                 SELECT t.N+1 FROM cteTally t WHERE SUBSTRING(@pString,t.N,1) = @pDelimiter
                ),
cteLen(N1,L1) AS(--==== Return start and length (for use in substring)
                 SELECT s.N1,
                        ISNULL(NULLIF(CHARINDEX(@pDelimiter,@pString,s.N1),0)-s.N1,8000)
                   FROM cteStart s
                )
--===== Do the actual split. The ISNULL/NULLIF combo handles the length for the final element when no delimiter is found.
 SELECT ItemNumber = ROW_NUMBER() OVER(ORDER BY l.N1),
        Item       = SUBSTRING(@pString, l.N1, l.L1)
   FROM cteLen l
;

TEST

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
 *
FROM #Testdata
OUTER APPLY [dbo].[DelimitedSplit](String,',');

DROP TABLE #Testdata;

结果

SomeID  OtherID String      ItemNumber  Item
1       9       18,20,22    1           18
1       9       18,20,22    2           20
1       9       18,20,22    3           22
2       8       17,19       1           17
2       8       17,19       2           19
3       7       13,19,20    1           13
3       7       13,19,20    2           19
3       7       13,19,20    3           20
4       6       1   
9       11      1,2,3,4     1           1
9       11      1,2,3,4     2           2
9       11      1,2,3,4     3           3
9       11      1,2,3,4     4           4

;WITH tmp(SomeID, OtherID, DataItem, Data) as (
    SELECT SomeID, OtherID, LEFT(Data, CHARINDEX(',',Data+',')-1),
        STUFF(Data, 1, CHARINDEX(',',Data+','), '')
FROM Testdata
WHERE Data > ''
)
SELECT SomeID, OtherID, Data
FROM tmp
ORDER BY SomeID

仅对上述查询进行了微小的修改…