终于，SQL Server 2016结束了等待。他们引入了Split字符串函数STRING_SPLIT:

select OtherID, cs.Value --SplitData
from yourtable
cross apply STRING_SPLIT (Data, ',') cs

所有其他方法来分割字符串，如XML, tallytable, while循环等。已经被STRING_SPLIT函数破坏了。

这里有一篇关于性能比较的优秀文章:性能惊喜和假设:STRING_SPLIT。

对于旧版本，这里使用的是一个分割字符串函数(可能的最佳方法)

CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover NVARCHAR(4000) 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, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --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 ;

从talley引用哦!一个改进的SQL 8K“CSV分配器”函数

很晚了，但是试试这个:

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

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

终于，SQL Server 2016结束了等待。他们引入了Split字符串函数STRING_SPLIT:

select OtherID, cs.Value --SplitData
from yourtable
cross apply STRING_SPLIT (Data, ',') cs

所有其他方法来分割字符串，如XML, tallytable, while循环等。已经被STRING_SPLIT函数破坏了。

这里有一篇关于性能比较的优秀文章:性能惊喜和假设:STRING_SPLIT。

对于旧版本，这里使用的是一个分割字符串函数(可能的最佳方法)

CREATE FUNCTION [dbo].[DelimitedSplit8K] (@pString VARCHAR(8000), @pDelimiter CHAR(1)) RETURNS TABLE WITH SCHEMABINDING AS RETURN --===== "Inline" CTE Driven "Tally Table" produces values from 0 up to 10,000... -- enough to cover NVARCHAR(4000) 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, E1 b), --10E+2 or 100 rows E4(N) AS (SELECT 1 FROM E2 a, E2 b), --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 ;

从talley引用哦!一个改进的SQL 8K“CSV分配器”函数

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

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