sql - 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函数不起作用，并且出现此错误：


只有在兼容级别130下，STRING_SPLIT函数才可用


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

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;