SQL Server 2012 中的 STRING_SPLIT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46902892/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
STRING_SPLIT in SQL Server 2012
提问by Moh
I have this parameter
我有这个参数
@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.
我想做一些事情来拆分逗号分隔的值。
The string_splitfunction doesn't work and I get this error:
该string_split功能不起作用，我收到此错误：
The STRING_SPLIT function is available only under compatibility level 130
STRING_SPLIT 函数仅在兼容级别 130 下可用
and I try to alter my database and set the compatibility to 130 but I don't have a permission for this change.
我尝试更改我的数据库并将兼容性设置为 130，但我无权进行此更改。
回答by Yogesh Sharma
Other approach is too use XMLMethod with CROSS APPLYto split your Comma Separated Data :
其他方法也是使用XMLMethod withCROSS 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);
Result :
结果 ：
DATA
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
Example :
例子 ：
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;
回答by Denis
Inline function based on Yogesh Sharmaand Salman Aanswers:
基于Yogesh Sharma和Salman A 的内联函数答案：
Create FUNCTION [dbo].[fn_split_string]
(
@string nvarchar(max),
@delimiter nvarchar(max)
)
/*
The same as STRING_SPLIT for compatibility level < 130
https://docs.microsoft.com/en-us/sql/t-sql/functions/string-split-transact-sql?view=sql-server-ver15
*/
RETURNS TABLE AS RETURN
(
SELECT
--ROW_NUMBER ( ) over(order by (select 0)) AS id -- intuitive, but not correect
Split.a.value('let $n := . return count(../*[. << $n]) + 1', 'int') AS id
, Split.a.value('.', 'NVARCHAR(MAX)') AS value
FROM
(
SELECT CAST('<X>'+REPLACE(@string, @delimiter, '</X><X>')+'</X>' AS XML) AS String
) AS a
CROSS APPLY String.nodes('/X') AS Split(a)
)
Example:
例子：
DECLARE @ID NVARCHAR(300)= 'abc,d,e,f,g';
select * from fn_split_string(@ID,',')
-- If you need exactly string_split functionality (without id column):
select value from fn_split_string(@ID,',')
回答by o.v
For those looking how to transform multi-line text into rows, here is a code based on the answer:
对于那些寻找如何将多行文本转换为行的人，这里是一个基于答案的代码：
declare @text varchar(max) = 'line0
line1
line2'
select split.a.value('.', 'nvarchar(max)') data
from
(
select cast('<x>' + replace(@text, char(13) + char(10), '</x><x>') + '</x>' as xml) as string
) as a
cross apply string.nodes('/x') as split(a)
回答by Al3x_M
Another approach would be to use CHARINDEX and SUBSTRING in a WHILE:
另一种方法是在 WHILE 中使用 CHARINDEX 和 SUBSTRING：
DECLARE @IDs VARCHAR(500);
DECLARE @Number VARCHAR(500);
DECLARE @charSpliter CHAR;
SET @charSpliter = ',';
SET @IDs = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20' + @charSpliter;
WHILE CHARINDEX(@charSpliter, @IDs) > 0
BEGIN
SET @Number = SUBSTRING(@IDs, 0, CHARINDEX(@charSpliter, @IDs));
SET @IDs = SUBSTRING(@IDs, CHARINDEX(@charSpliter, @IDs) + 1, LEN(@IDs));
PRINT @Number;
END;
回答by Didier68
A little variation of @Al3x_M's polyfill, when it is not possible to change the database compatibility level : I use a TABLEvariable to store the list of value, for using them later in another query:
@Al3x_M 的 polyfill 的一个小变化，当无法更改数据库兼容性级别时：我使用一个TABLE变量来存储值列表，以便稍后在另一个查询中使用它们：
DECLARE @IDs VARCHAR(500);
SET @IDs = '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,2a0' ;
declare @list TABLE (id int);
DECLARE @Number int, @idx int
DECLARE @charSpliter CHAR;
SET @charSpliter = ','
SET @IDs = @IDs + @charSpliter;
set @idx = 0
WHILE (1 = 1)
BEGIN
set @idx = CHARINDEX(@charSpliter, @IDs)
if (@idx is NULL or @idx <= 0) break;
BEGIN TRY
SET @Number = SUBSTRING(@IDs, 0, @idx)
SET @IDs = SUBSTRING(@IDs, @idx + 1, LEN(@IDs))
insert @list select convert(int, @Number)
END TRY
BEGIN CATCH
break
END CATCH
END
-- @list available for the next query...
select * from @list
回答by hamzox
If your database compatibility level is lower than 130, SQL Server will not be able to find and execute STRING_SPLITfunction. You can change a compatibility level of database using the following command:
如果您的数据库兼容级别低于130，SQL Server 将无法查找和执行STRING_SPLIT函数。您可以使用以下命令更改数据库的兼容级别：
ALTER DATABASE DatabaseName SET COMPATIBILITY_LEVEL = 130
Note that compatibility level 120 might be default even in new Azure SQL Databases.
请注意，即使在新的Azure SQL 数据库中，兼容性级别 120 也可能是默认值。
For reference:
以供参考：
Version- Highest Compatibility Level - Lowest Available Level
版本- 最高兼容性级别 - 最低可用级别
SQL 2017 - 140 - 100
SQL 2016 - 130 - 100
SQL 2014 - 120 - 100
SQL 2012 - 110 - 90
SQL 2008 - 100 - 80
SQL 2005 - 90 - 80
SQL 2000 - 80 - 80
SQL 2017 - 140 - 100
SQL 2016 - 130 - 100
SQL 2014 - 120 - 100
SQL 2012 - 110 - 90
SQL 2008 - 100 - 80
SQL 2005 - 90 - 80
SQL 80 - 00
Also, check your syntax as well like:
另外，请检查您的语法，例如：
SELECT Value FROM STRING_SPLIT('Lorem ipsum dolor sit amet.', ' ');
回答by Joey Morgan
I made this as a quick and dirty substitute using the table approach so the end user can select which of the sections they want. The original string can be used in a join or the individual row selected for a scalar result. Tested in
我使用表格方法将其作为快速而肮脏的替代品，以便最终用户可以选择他们想要的部分。原始字符串可用于连接或为标量结果选择的单个行。测试在
Microsoft SQL Server 2012 (SP4-OD) (KB4091266) - 11.0.7469.6 (X64) Feb 28 2018 17:47:20 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor)
Microsoft SQL Server 2012 (SP4-OD) (KB4091266) - 11.0.7469.6 (X64) 2018 年 2 月 28 日 17:47:20 版权所有 (c) Microsoft Corporation Enterprise Edition（64 位），Windows NT 6.3（内部版本 9600：）（管理程序）
SET QUOTED_IDENTIFIER ON; SET ANSI_NULLS ON; GO
SET QUOTED_IDENTIFIER ON; 设置 ANSI_NULLS 开；走
Create FUNCTION dbo.StringSplit2012 ( @OriginalString VARCHAR(500) ,@Separator VARCHAR(6) ) RETURNS @Sections TABLE ( OriginalString VARCHAR(500) NOT NULL ,StringSection VARCHAR(500) NULL ,SectionNumber INT ) AS BEGIN DECLARE @SectionCount INT;
创建 FUNCTION dbo.StringSplit2012 ( @OriginalString VARCHAR(500) ,@Separator VARCHAR(6) ) 返回@Sections TABLE ( OriginalString VARCHAR(500) NOT NULL ,StringSection VARCHAR(500) NULL ,SectionNumber INT ) AS BEGINCountCLARE;
DECLARE @LoopCounter INT = 1;
DECLARE @RemainingString VARCHAR(500);
DECLARE @CurrentSection VARCHAR(500);
SET @SectionCount =
LEN (@OriginalString) - LEN (REPLACE (@OriginalString, @Separator, ''));
IF @SectionCount = 0
BEGIN
INSERT INTO
@Sections
(
OriginalString
,StringSection
,SectionNumber
)
VALUES
(@OriginalString -- OriginalString - varchar(500)
,@OriginalString -- StringSection - varchar(500)
,1 -- SectionNumber - int
);
END;
ELSE
BEGIN
SET @RemainingString = @OriginalString;
DECLARE @SectionStart INT;
DECLARE @SectionLength INT;
WHILE @LoopCounter <= @SectionCount
BEGIN
SET @SectionStart = 1;
SET @SectionLength = CHARINDEX (@Separator, @RemainingString);
SET @CurrentSection = LEFT(@RemainingString, @SectionLength - 1);
INSERT INTO
@Sections
(
OriginalString
,StringSection
,SectionNumber
)
VALUES
(@OriginalString
,@CurrentSection
,@LoopCounter -- SectionNumber - int
);
SET @RemainingString =
RIGHT(@RemainingString, LEN (@RemainingString) - @SectionLength);
SET @LoopCounter = @LoopCounter + 1;
END;
DECLARE @TotalParsedLength INT =
(
SELECT SUM ( LEN (s.StringSection)) FROM @Sections AS s
) + @SectionCount;
SET @CurrentSection =
RIGHT(@RemainingString, LEN (@OriginalString) - @TotalParsedLength);
INSERT INTO
@Sections
(
OriginalString
,StringSection
,SectionNumber
)
VALUES
(@OriginalString
,@CurrentSection
,@LoopCounter -- SectionNumber - int
);
END;
RETURN;
END;
GO
走
I hope this saves someone some time. I use STRING_SPLIT in the function I created to give me the package name from a job step's command, and it blew up when I moved to my 2012 server. So I wrote my own. (Like ya do!)
我希望这可以节省一些时间。我在我创建的函数中使用 STRING_SPLIT 来从作业步骤的命令中给我包名，当我移动到我的 2012 服务器时它爆炸了。所以我写了我自己的。（就像你一样！）
Joey Morgan
乔伊摩根
BI/Integrations Developer III
BI/集成开发人员 III
Aspen Dental Management, Inc
阿斯彭牙科管理公司
Syracuse, NY
纽约州锡拉丘兹
