SQL Server 2012 中的 STRING_SPLIT

Question

我有这个参数

@ID varchar = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20'

我想做一些事情来拆分逗号分隔的值。

string_split 功能不起作用，我收到此错误：

STRING_SPLIT 函数仅在兼容级别 130 下可用

我尝试更改我的数据库并将兼容性设置为 130，但我没有此更改的权限。

原文由 Moh 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

其他方法是使用 XML 方法和 CROSS APPLY 来拆分逗号分隔数据：

 SELECT Split.a.value('.', 'NVARCHAR(MAX)') DATA
FROM
(
    SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
) AS A
CROSS APPLY String.nodes('/X') AS Split(a);

结果 ：

 DATA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

例子 ：

 DECLARE @ID NVARCHAR(300)= '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20';
DECLARE @Marks NVARCHAR(300)= '0,1,2,5,8,9,4,6,7,3,5,2,7,1,9,4,0,2,5,0';
DECLARE @StudentsMark TABLE
(id    NVARCHAR(300),
 marks NVARCHAR(300)
);
--insert into @StudentsMark
;WITH CTE
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') id,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST('<X>'+REPLACE(@ID, ',', '</X><X>')+'</X>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a)),
     CTE1
     AS (
     SELECT Split.a.value('.', 'NVARCHAR(MAX)') marks,
            ROW_NUMBER() OVER(ORDER BY
                             (
                                 SELECT NULL
                             )) RN
     FROM
     (
         SELECT CAST('<X>'+REPLACE(@Marks, ',', '</X><X>')+'</X>' AS XML) AS String
     ) AS A
     CROSS APPLY String.nodes('/X') AS Split(a))
     INSERT INTO @StudentsMark
            SELECT C.id,
                   C1.marks
            FROM CTE C
                 LEFT JOIN CTE1 C1 ON C1.RN = C.RN;
SELECT *
FROM @StudentsMark;

原文由 Yogesh Sharma 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

谢谢你 al3x-m 。我创建了这个函数，以便在很多时候重复使用。

 CREATE FUNCTION STRING_SPLIT_POLYFILL2016 (
    @string NVARCHAR(4000)
    ,@separator NVARCHAR(4000)
    )
RETURNS @T TABLE (ColName VARCHAR(4000))
AS
BEGIN
    /*
pitt phunsanit
polyfill of STRING_SPLIT in SQL Server 2016
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
https://stackoverflow.com/questions/46902892/string-split-in-sql-server-2012
*/
    DECLARE @Number VARCHAR(4000);

    SET @string = @string + @separator;

    WHILE CHARINDEX(@separator, @string) > 0
    BEGIN
        SET @Number = SUBSTRING(@string, 0, CHARINDEX(@separator, @string));
        SET @string = SUBSTRING(@string, CHARINDEX(@separator, @string) + 1, LEN(@string));

        INSERT INTO @T (ColName)
        VALUES (@Number)
    END

    RETURN
END

-- 演示

DECLARE @IDS NVARCHAR(4000)

SET @IDS = "pitt,phunsanit,01,02,03,4,5,6,7,8,9,10,พิชญ์,พันธุ์สนิท"

SELECT *
FROM STRING_SPLIT_POLYFILL2016(@IDS, ",")

dbfiddle 上的现场演示

原文由 phunsanit 发布，翻译遵循 CC BY-SA 4.0 许可协议