SQL Server 2012 中的 STRING_SPLIT [英] STRING_SPLIT in SQL Server 2012
本文介绍了SQL Server 2012 中的 STRING_SPLIT的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我有这个参数
@ID varchar = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20'
我想做点什么来分割逗号分隔的值.
I want to do something to split the comma-separated values.
string_split
函数不起作用,我收到此错误:
The string_split
function doesn't work and I get this error:
STRING_SPLIT 函数仅在兼容级别 130 下可用
The STRING_SPLIT function is available only under compatibility level 130
并且我尝试更改我的数据库并将兼容性设置为 130,但我无权进行此更改.
and I try to alter my database and set the compatibility to 130 but I don't have a permission for this change.
推荐答案
其他方法也是使用 XML
方法和 CROSS APPLY
来拆分逗号分隔数据:>
Other approach is too use XML
Method with CROSS APPLY
to split your Comma Separated Data :
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;
这篇关于SQL Server 2012 中的 STRING_SPLIT的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文