T-SQL 拆分字符串

我有一个 SQLServer2008R2列包含一个字符串，我需要一个逗号分割。我在 StackOverflow 上看到过很多答案，但是没有一个能在 R2中工作。我已经确保对任何拆分函数示例拥有选择权限。非常感谢你的帮助。

606009 次浏览

小开

最佳答案

我以前使用过这个 SQL，它可能对你有用:-

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN


DECLARE @name NVARCHAR(255)
DECLARE @pos INT


WHILE CHARINDEX(',', @stringToSplit) > 0
BEGIN
SELECT @pos  = CHARINDEX(',', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)


INSERT INTO @returnList
SELECT @name


SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END


INSERT INTO @returnList
SELECT @stringToSplit


RETURN
END

并使用它:-

SELECT * FROM dbo.splitstring('91,12,65,78,56,789')

小开

如果你替换

WHILE CHARINDEX(',', @stringToSplit) > 0

和

WHILE LEN(@stringToSplit) > 0

您可以消除 while 循环后的最后一个 insert！

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN


DECLARE @name NVARCHAR(255)
DECLARE @pos INT


WHILE LEN(@stringToSplit) > 0
BEGIN
SELECT @pos  = CHARINDEX(',', @stringToSplit)




if @pos = 0
SELECT @pos = LEN(@stringToSplit)




SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)


INSERT INTO @returnList
SELECT @name


SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END


RETURN
END

小开

我最近不得不写这样的东西。这是我想到的解决办法。它对于任何分隔符字符串都是通用的，我认为它的性能会稍微好一点:

CREATE FUNCTION [dbo].[SplitString]
( @string nvarchar(4000)
, @delim nvarchar(100) )
RETURNS
@result TABLE
( [Value] nvarchar(4000) NOT NULL
, [Index] int NOT NULL )
AS
BEGIN
DECLARE @str nvarchar(4000)
, @pos int
, @prv int = 1


SELECT @pos = CHARINDEX(@delim, @string)
WHILE @pos > 0
BEGIN
SELECT @str = SUBSTRING(@string, @prv, @pos - @prv)
INSERT INTO @result SELECT @str, @prv


SELECT @prv = @pos + LEN(@delim)
, @pos = CHARINDEX(@delim, @string, @pos + 1)
END


INSERT INTO @result SELECT SUBSTRING(@string, @prv, 4000), @prv
RETURN
END

小开

一个使用 CTE 的解决方案，如果有人需要的话(除了我，谁显然需要，这就是我写它的原因)。

declare @StringToSplit varchar(100) = 'Test1,Test2,Test3';
declare @SplitChar varchar(10) = ',';


with StringToSplit as (
select
ltrim( rtrim( substring( @StringToSplit, 1, charindex( @SplitChar, @StringToSplit ) - 1 ) ) ) Head
, substring( @StringToSplit, charindex( @SplitChar, @StringToSplit ) + 1, len( @StringToSplit ) ) Tail


union all


select
ltrim( rtrim( substring( Tail, 1, charindex( @SplitChar, Tail ) - 1 ) ) ) Head
, substring( Tail, charindex( @SplitChar, Tail ) + 1, len( Tail ) ) Tail
from StringToSplit
where charindex( @SplitChar, Tail ) > 0


union all


select
ltrim( rtrim( Tail ) ) Head
, '' Tail
from StringToSplit
where charindex( @SplitChar, Tail ) = 0
and len( Tail ) > 0
)
select Head from StringToSplit

小开

ALTER FUNCTION [dbo].func_split_string
(
@input as varchar(max),
@delimiter as varchar(10) = ";"


)
RETURNS @result TABLE
(
id smallint identity(1,1),
csv_value varchar(max) not null
)
AS
BEGIN
DECLARE @pos AS INT;
DECLARE @string AS VARCHAR(MAX) = '';


WHILE LEN(@input) > 0
BEGIN
SELECT @pos = CHARINDEX(@delimiter,@input);


IF(@pos<=0)
select @pos = len(@input)


IF(@pos <> LEN(@input))
SELECT @string = SUBSTRING(@input, 1, @pos-1);
ELSE
SELECT @string = SUBSTRING(@input, 1, @pos);


INSERT INTO @result SELECT @string


SELECT @input = SUBSTRING(@input, @pos+len(@delimiter), LEN(@input)-@pos)
END
RETURN
END

小开

除了递归 CTE 和 while 循环，有人考虑过更基于集的方法吗？注意，此函数是为该问题编写的，该问题基于 SQLServer2008和 逗号作为分隔符。在 SQLServer2016及以上版本(以及兼容级别130及以上版本)中，STRING_SPLIT()是一个更好的选择。

CREATE FUNCTION dbo.SplitString
(
@List     nvarchar(max),
@Delim    nvarchar(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT [Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_columns) AS x WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], DATALENGTH(@Delim)/2) = @Delim
) AS y
);
GO

如果您想避免字符串长度 < = sys.all_columns中的行数的限制(SQL Server 2017中 model中的行数为9,980; 您自己的用户数据库中的行数要高得多) ，那么您可以使用其他方法来推导这些数字，比如构建您自己的数字表。在无法使用系统表或创建自己的表的情况下，也可以使用递归 CTE:

CREATE FUNCTION dbo.SplitString
(
@List     nvarchar(max),
@Delim    nvarchar(255)
)
RETURNS TABLE WITH SCHEMABINDING
AS
RETURN ( WITH n(n) AS (SELECT 1 UNION ALL SELECT n+1
FROM n WHERE n <= LEN(@List))
SELECT [Value] = SUBSTRING(@List, n,
CHARINDEX(@Delim, @List + @Delim, n) - n)
FROM n WHERE n <= LEN(@List)
AND SUBSTRING(@Delim + @List, n, DATALENGTH(@Delim)/2) = @Delim
);
GO

但是您必须将 OPTION (MAXRECURSION 0)(或 MAXRECURSION <longest possible string length if < 32768>)附加到外部查询，以避免字符串大于100个字符的递归错误。如果这也不是一个好的选择，那么请参阅这个答案，如果您需要命令分割字符串函数，请参阅这个答案。

(此外，分隔符将必须是 NCHAR(<=1228)。仍在研究为什么。)

更多关于拆分函数的内容，为什么循环和递归 CTE 不能伸缩，以及更好的选择，如果你拆分来自应用层的字符串:

分裂弦乐

小开

这是更狭义的裁剪。当我这样做的时候，我通常有一个逗号分隔的唯一 id 列表(INT 或 BIGINT) ，我想把它强制转换为一个表，用作另一个主键为 INT 或 BIGINT 的表的内部连接。我希望返回一个内联的表值函数，这样我就有了尽可能高效的连接。

样本用量如下:

 DECLARE @IDs VARCHAR(1000);
SET @IDs = ',99,206,124,8967,1,7,3,45234,2,889,987979,';
SELECT me.Value
FROM dbo.MyEnum me
INNER JOIN dbo.GetIntIdsTableFromDelimitedString(@IDs) ids ON me.PrimaryKey = ids.ID

我从 http://sqlrecords.blogspot.com/2012/11/converting-delimited-list-to-table.html中窃取了这个想法，将其改为内联表值并强制转换为 INT。

create function dbo.GetIntIDTableFromDelimitedString
(
@IDs VARCHAR(1000)  --this parameter must start and end with a comma, eg ',123,456,'
--all items in list must be perfectly formatted or function will error
)
RETURNS TABLE AS
RETURN


SELECT
CAST(SUBSTRING(@IDs,Nums.number + 1,CHARINDEX(',',@IDs,(Nums.number+2)) - Nums.number - 1) AS INT) AS ID
FROM
[master].[dbo].[spt_values] Nums
WHERE Nums.Type = 'P'
AND    Nums.number BETWEEN 1 AND DATALENGTH(@IDs)
AND    SUBSTRING(@IDs,Nums.number,1) = ','
AND    CHARINDEX(',',@IDs,(Nums.number+1)) > Nums.number;


GO

小开

这里有一个版本，可以分割模式使用 patindex，一个简单的改编上面的帖子。我有一个案例，我需要拆分一个包含多个分隔符字符的字符串。


alter FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(1000), @splitPattern varchar(10) )
RETURNS
@returnList TABLE ([Name] [nvarchar] (500))
AS
BEGIN


DECLARE @name NVARCHAR(255)
DECLARE @pos INT


WHILE PATINDEX(@splitPattern, @stringToSplit) > 0
BEGIN
SELECT @pos  = PATINDEX(@splitPattern, @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)


INSERT INTO @returnList
SELECT @name


SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END


INSERT INTO @returnList
SELECT @stringToSplit


RETURN
END
select * from dbo.splitstring('stringa/stringb/x,y,z','%[/,]%');

结果是这样的

Stringa Stringb X 嘿 Z

小开

我需要一个快速的方法来摆脱从邮编的 +4。

UPDATE #Emails
SET ZIPCode = SUBSTRING(ZIPCode, 1, (CHARINDEX('-', ZIPCODE)-1))
WHERE ZIPCode LIKE '%-%'

没有 proc... 没有 UDF... 只有一个紧凑的小型内联命令，该命令执行必须执行的任务。

根据需要更改分隔符等，它将适用于任何情况。

小开

我个人使用这个函数:

ALTER FUNCTION [dbo].[CUST_SplitString]
(
@String NVARCHAR(4000),
@Delimiter NCHAR(1)
)
RETURNS TABLE
AS
RETURN
(
WITH Split(stpos,endpos)
AS(
SELECT 0 AS stpos, CHARINDEX(@Delimiter,@String) AS endpos
UNION ALL
SELECT endpos+1, CHARINDEX(@Delimiter,@String,endpos+1)
FROM Split
WHERE endpos > 0
)
SELECT 'Id' = ROW_NUMBER() OVER (ORDER BY (SELECT 1)),
'Data' = SUBSTRING(@String,stpos,COALESCE(NULLIF(endpos,0),LEN(@String)+1)-stpos)
FROM Split
)

小开

我已经开发了一个双拆分器(需要两个拆分字符)按照要求给你。在这个线程中可能有一些价值，因为它是与字符串分割相关的查询中被引用最多的线程。

CREATE FUNCTION uft_DoubleSplitter
(
-- Add the parameters for the function here
@String VARCHAR(4000),
@Splitter1 CHAR,
@Splitter2 CHAR
)
RETURNS @Result TABLE (Id INT,MId INT,SValue VARCHAR(4000))
AS
BEGIN
DECLARE @FResult TABLE(Id INT IDENTITY(1, 1),
SValue VARCHAR(4000))
DECLARE @SResult TABLE(Id INT IDENTITY(1, 1),
MId INT,
SValue VARCHAR(4000))
SET @String = @String+@Splitter1


WHILE CHARINDEX(@Splitter1, @String) > 0
BEGIN
DECLARE @WorkingString VARCHAR(4000) = NULL


SET @WorkingString = SUBSTRING(@String, 1, CHARINDEX(@Splitter1, @String) - 1)
--Print @workingString


INSERT INTO @FResult
SELECT CASE
WHEN @WorkingString = '' THEN NULL
ELSE @WorkingString
END


SET @String = SUBSTRING(@String, LEN(@WorkingString) + 2, LEN(@String))


END
IF ISNULL(@Splitter2, '') != ''
BEGIN
DECLARE @OStartLoop INT
DECLARE @OEndLoop INT


SELECT @OStartLoop = MIN(Id),
@OEndLoop = MAX(Id)
FROM @FResult


WHILE @OStartLoop <= @OEndLoop
BEGIN
DECLARE @iString VARCHAR(4000)
DECLARE @iMId INT


SELECT @iString = SValue+@Splitter2,
@iMId = Id
FROM @FResult
WHERE Id = @OStartLoop


WHILE CHARINDEX(@Splitter2, @iString) > 0
BEGIN
DECLARE @iWorkingString VARCHAR(4000) = NULL


SET @IWorkingString = SUBSTRING(@iString, 1, CHARINDEX(@Splitter2, @iString) - 1)


INSERT INTO @SResult
SELECT @iMId,
CASE
WHEN @iWorkingString = '' THEN NULL
ELSE @iWorkingString
END


SET @iString = SUBSTRING(@iString, LEN(@iWorkingString) + 2, LEN(@iString))


END


SET @OStartLoop = @OStartLoop + 1
END
INSERT INTO @Result
SELECT MId AS PrimarySplitID,
ROW_NUMBER() OVER (PARTITION BY MId ORDER BY Mid, Id) AS SecondarySplitID ,
SValue
FROM @SResult
END
ELSE
BEGIN
INSERT INTO @Result
SELECT Id AS PrimarySplitID,
NULL AS SecondarySplitID,
SValue
FROM @FResult
END
RETURN

用法:

--FirstSplit
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&',NULL)


--Second Split
SELECT * FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===','&','=')

可能的用法(获取每个拆分的第二个值) :

SELECT fn.SValue
FROM uft_DoubleSplitter('ValueA=ValueB=ValueC=ValueD==ValueE&ValueA=ValueB=ValueC===ValueE&ValueA=ValueB==ValueD===', '&', '=')AS fn
WHERE fn.mid = 2

小开

最简单的方法是使用 XML格式。

1. 将字符串转换为不带表的行

查询

DECLARE @String varchar(100) = 'String1,String2,String3'
-- To change ',' to any other delimeter, just change ',' to your desired one
DECLARE @Delimiter CHAR = ','


SELECT LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
FROM
(
SELECT CAST ('<M>' + REPLACE(@String, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)

结果

 x---------x
| Value   |
x---------x
| String1 |
| String2 |
| String3 |
x---------x

2. 从每个 CSV 行具有 ID 的表转换为行

资料来源表

 x-----x--------------------------x
| Id  |           Value          |
x-----x--------------------------x
|  1  |  String1,String2,String3 |
|  2  |  String4,String5,String6 |
x-----x--------------------------x

查询

-- To change ',' to any other delimeter, just change ',' before '</M><M>' to your desired one
DECLARE @Delimiter CHAR = ','


SELECT ID,LTRIM(RTRIM(Split.a.value('.', 'VARCHAR(100)'))) 'Value'
FROM
(
SELECT ID,CAST ('<M>' + REPLACE(VALUE, @Delimiter, '</M><M>') + '</M>' AS XML) AS Data
FROM TABLENAME
) AS A
CROSS APPLY Data.nodes ('/M') AS Split(a)

结果

 x-----x----------x
| Id  |  Value   |
x-----x----------x
|  1  |  String1 |
|  1  |  String2 |
|  1  |  String3 |
|  2  |  String4 |
|  2  |  String5 |
|  2  |  String6 |
x-----x----------x

小开

这里有一个正确的版本，但我认为最好添加一点容错，以防他们有一个后面的逗号，以及使它，这样你就可以使用它不是作为一个函数，而是作为一个更大的代码片段的一部分。以防您只使用它一次，不需要函数。这也适用于整数(这正是我所需要的) ，因此您可能需要更改数据类型。

DECLARE @StringToSeperate VARCHAR(10)
SET @StringToSeperate = '1,2,5'


--SELECT @StringToSeperate IDs INTO #Test


DROP TABLE #IDs
CREATE TABLE #IDs (ID int)


DECLARE @CommaSeperatedValue NVARCHAR(255) = ''
DECLARE @Position INT = LEN(@StringToSeperate)


--Add Each Value
WHILE CHARINDEX(',', @StringToSeperate) > 0
BEGIN
SELECT @Position  = CHARINDEX(',', @StringToSeperate)
SELECT @CommaSeperatedValue = SUBSTRING(@StringToSeperate, 1, @Position-1)


INSERT INTO #IDs
SELECT @CommaSeperatedValue


SELECT @StringToSeperate = SUBSTRING(@StringToSeperate, @Position+1, LEN(@StringToSeperate)-@Position)


END


--Add Last Value
IF (LEN(LTRIM(RTRIM(@StringToSeperate)))>0)
BEGIN
INSERT INTO #IDs
SELECT SUBSTRING(@StringToSeperate, 1, @Position)
END


SELECT * FROM #IDs

小开

我稍微修改了一下 + Andy Robinson 的函数，现在你可以从返回表中选择只需要的部分:

CREATE FUNCTION dbo.splitstring ( @stringToSplit VARCHAR(MAX) )


RETURNS


@returnList TABLE ([numOrder] [tinyint] , [Name] [nvarchar] (500)) AS
BEGIN


DECLARE @name NVARCHAR(255)


DECLARE @pos INT


DECLARE @orderNum INT


SET @orderNum=0


WHILE CHARINDEX('.', @stringToSplit) > 0


BEGIN
SELECT @orderNum=@orderNum+1;
SELECT @pos  = CHARINDEX('.', @stringToSplit)
SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1)


INSERT INTO @returnList
SELECT @orderNum,@name


SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos)
END
SELECT @orderNum=@orderNum+1;
INSERT INTO @returnList
SELECT @orderNum, @stringToSplit


RETURN
END




Usage:

从 dbo.splitstring (“ elis.yd.crp1.1.cba.mdsp.t389.bt”)中选择名称，其中 numOrder = 5

小开

你可使用以下功能:

        CREATE FUNCTION SplitString
(
@Input NVARCHAR(MAX),
@Character CHAR(1)
)
RETURNS @Output TABLE (
Item NVARCHAR(1000)
)
AS
BEGIN


DECLARE @StartIndex INT, @EndIndex INT
SET @StartIndex = 1
IF SUBSTRING(@Input, LEN(@Input) - 1, LEN(@Input)) <> @Character
BEGIN
SET @Input = @Input + @Character
END


WHILE CHARINDEX(@Character, @Input) > 0
BEGIN
SET @EndIndex = CHARINDEX(@Character, @Input)


INSERT INTO @Output(Item)
SELECT SUBSTRING(@Input, @StartIndex, @EndIndex - 1)


SET @Input = SUBSTRING(@Input, @EndIndex + 1, LEN(@Input))
END


RETURN
END
GO

小开

最简单的方法:

安装 SQLServer2016
使用 STRING _ SPLIT https://msdn.microsoft.com/en-us/library/mt684588.aspx

它甚至可以在快版中使用:)。

小开

最后，在 SQLServer2016中等待结束了，他们引入了拆分字符串函数: 强 > STRING_SPLIT强

select * From STRING_SPLIT ('a,b', ',') cs

所有其他分割字符串的方法，如 XML、 Tally 表、 while 循环等等。已经被这个 STRING_SPLIT函数吹走了。

下面是一篇带有性能比较的优秀文章: 表现惊喜与假设: STRING _ SPLIT

小开

下面是一个例子，您可以将其用作函数，也可以将相同的逻辑放入过程中。 —— SELECT * from [ dbo ] . fn _ SplitString;

CREATE FUNCTION [dbo].[fn_SplitString] (@CSV VARCHAR(MAX), @Delimeter VARCHAR(100) = ',') RETURNS @retTable TABLE ( [value] VARCHAR(MAX) NULL )AS BEGIN DECLARE @vCSV VARCHAR (MAX) = @CSV, @vDelimeter VARCHAR (100) = @Delimeter; IF @vDelimeter = ';' BEGIN SET @vCSV = REPLACE(@vCSV, ';', '~!~#~'); SET @vDelimeter = REPLACE(@vDelimeter, ';', '~!~#~'); END; SET @vCSV = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@vCSV, '&', '&'), '<', '<'), '>', '>'), '''', '''), '"', '"'); DECLARE @xml XML; SET @xml = '' + REPLACE(@vCSV, @vDelimeter, '') + ''; INSERT INTO @retTable SELECT x.i.value('.', 'varchar(max)') AS COLUMNNAME FROM @xml.nodes('//i')AS x(i); RETURN; END;

小开

/*

回答 T-SQL 拆分字符串
基于安迪 · 罗宾逊和 AviG 的回答
增强的功能参考: < a href = “ https://stackoverflow. com/q/2025585/4228193”> LEN 函数不包括 SQL Server 中的尾随空格
这个“文件”应该同时作为标记文件和 SQL 文件 
有效

*/ CREATE FUNCTION dbo.splitstring ( --CREATE OR ALTER @stringToSplit NVARCHAR(MAX) ) RETURNS @returnList TABLE ([Item] NVARCHAR (MAX)) AS BEGIN DECLARE @name NVARCHAR(MAX) DECLARE @pos BIGINT SET @stringToSplit = @stringToSplit + ',' -- this should allow entries that end with a `,` to have a blank value in that "column" WHILE ((LEN(@stringToSplit+'_') > 1)) BEGIN -- `+'_'` gets around LEN trimming terminal spaces. See URL referenced above SET @pos = COALESCE(NULLIF(CHARINDEX(',', @stringToSplit),0),LEN(@stringToSplit+'_')) -- COALESCE grabs first non-null value SET @name = SUBSTRING(@stringToSplit, 1, @pos-1) --MAX size of string of type nvarchar is 4000 SET @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, 4000) -- With SUBSTRING fn (MS web): "If start is greater than the number of characters in the value expression, a zero-length expression is returned." INSERT INTO @returnList SELECT @name --additional debugging parameters below can be added -- + ' pos:' + CAST(@pos as nvarchar) + ' remain:''' + @stringToSplit + '''(' + CAST(LEN(@stringToSplit+'_')-1 as nvarchar) + ')' END RETURN END GO /*

测试用例: 参见上面“增强功能”引用的 URL

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,b')

Item | L --- | --- a | 1 | 0 b | 1

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,,')

Item | L --- | --- a | 1 | 0 | 0

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, ')

Item | L --- | --- a | 1 | 0 | 1

SELECT *,LEN(Item+'_')-1 'L' from splitstring('a,, c ')

Item | L --- | --- a | 1 | 0 c | 3

*/

小开

所有使用某种循环(迭代)进行字符串分割的函数性能都很差。它们应该被基于集合的解决方案所替代。

这段代码执行得非常好。

CREATE FUNCTION dbo.SplitStrings ( @List NVARCHAR(MAX), @Delimiter NVARCHAR(255) ) RETURNS TABLE WITH SCHEMABINDING AS RETURN ( SELECT Item = y.i.value('(./text())[1]', 'nvarchar(4000)') FROM ( SELECT x = CONVERT(XML, '' + REPLACE(@List, @Delimiter, '') + '').query('.') ) AS a CROSS APPLY x.nodes('i') AS y(i) ); GO

小开

使用 XML 元素的常用方法在遇到禁用字符时中断。这是一种将此方法与任何类型的字符一起使用的方法，即使使用分号作为分隔符也是如此。

诀窍在于，首先使用 SELECT SomeString AS [*] FOR XML PATH('')来正确转义所有禁用字符。这就是为什么我将分隔符替换为 神奇的价值，以避免与 ;作为分隔符的麻烦。

DECLARE @Dummy TABLE (ID INT, SomeTextToSplit NVARCHAR(MAX)) INSERT INTO @Dummy VALUES (1,N'A&B;C;D;E, F') ,(2,N'"C" & ''D'';<C>;D;E, F'); DECLARE @Delimiter NVARCHAR(10)=';'; --special effort needed (due to entities coding with "&code;")! WITH Casted AS ( SELECT * ,CAST(N'<x>' + REPLACE((SELECT REPLACE(SomeTextToSplit,@Delimiter,N'§§Split$me$here§§') AS [*] FOR XML PATH('')),N'§§Split$me$here§§',N'</x><x>') + N'</x>' AS XML) AS SplitMe FROM @Dummy ) SELECT Casted.ID ,x.value(N'.',N'nvarchar(max)') AS Part FROM Casted CROSS APPLY SplitMe.nodes(N'/x') AS A(x)

结果

ID Part 1 A&B 1 C 1 D 1 E, F 2 "C" & 'D' 2 <C> 2 D 2 E, F

小开

一种基于递归的 cte 解决方案

declare @T table (iden int identity, col1 varchar(100)); insert into @T(col1) values ('ROOT/South America/Lima/Test/Test2') , ('ROOT/South America/Peru/Test/Test2') , ('ROOT//South America/Venuzuala ') , ('RtT/South America / ') , ('ROOT/South Americas// '); declare @split char(1) = '/'; select @split as split; with cte as ( select t.iden, case when SUBSTRING(REVERSE(rtrim(t.col1)), 1, 1) = @split then LTRIM(RTRIM(t.col1)) else LTRIM(RTRIM(t.col1)) + @split end as col1, 0 as pos , 1 as cnt from @T t union all select t.iden, t.col1 , charindex(@split, t.col1, t.pos + 1), cnt + 1 from cte t where charindex(@split, t.col1, t.pos + 1) > 0 ) select t1.*, t2.pos, t2.cnt , ltrim(rtrim(SUBSTRING(t1.col1, t1.pos+1, t2.pos-t1.pos-1))) as bingo from cte t1 join cte t2 on t2.iden = t1.iden and t2.cnt = t1.cnt+1 and t2.pos > t1.pos order by t1.iden, t1.cnt;

小开

如果您需要一个最少代码的常见情况下的快速特别解决方案，那么这个递归 CTE 两行程序就可以做到:

DECLARE @s VARCHAR(200) = ',1,2,,3,,,4,,,,5,' ;WITH a AS (SELECT i=-1, j=0 UNION ALL SELECT j, CHARINDEX(',', @s, j + 1) FROM a WHERE j > i), b AS (SELECT SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0) SELECT * FROM b

要么将它作为一个独立的语句使用，要么将上面的 CTE 添加到任何查询中，这样您就可以将结果表 b与其他表连接起来，以便在任何进一步的表达式中使用。

编辑(作者 Shnugo)

如果添加一个计数器，您将得到一个位置索引和 List:

DECLARE @s VARCHAR(200) = '1,2333,344,4' ;WITH a AS (SELECT n=0, i=-1, j=0 UNION ALL SELECT n+1, j, CHARINDEX(',', @s, j+1) FROM a WHERE j > i), b AS (SELECT n, SUBSTRING(@s, i+1, IIF(j>0, j, LEN(@s)+1)-i-1) s FROM a WHERE i >= 0) SELECT * FROM b;

结果是:

n s 1 1 2 2333 3 344 4 4

小开

无意冒犯@AviG，这是他设计的功能的无错误版本，可以完整返回所有令牌。

IF EXISTS (SELECT * FROM sys.objects WHERE type = 'TF' AND name = 'TF_SplitString') DROP FUNCTION [dbo].[TF_SplitString] GO -- ============================================= -- Author: AviG -- Amendments: Parameterize the delimeter and included the missing chars in last token - Gemunu Wickremasinghe -- Description: Tabel valued function that Breaks the delimeted string by given delimeter and returns a tabel having split results -- Usage -- select * from [dbo].[TF_SplitString]('token1,token2,,,,,,,,token969',',') -- 969 items should be returned -- select * from [dbo].[TF_SplitString]('4672978261,4672978255',',') -- 2 items should be returned -- ============================================= CREATE FUNCTION dbo.TF_SplitString ( @stringToSplit VARCHAR(MAX) , @delimeter char = ',' ) RETURNS @returnList TABLE ([Name] [nvarchar] (500)) AS BEGIN DECLARE @name NVARCHAR(255) DECLARE @pos INT WHILE LEN(@stringToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(@delimeter, @stringToSplit) if @pos = 0 BEGIN SELECT @pos = LEN(@stringToSplit) SELECT @name = SUBSTRING(@stringToSplit, 1, @pos) END else BEGIN SELECT @name = SUBSTRING(@stringToSplit, 1, @pos-1) END INSERT INTO @returnList SELECT @name SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos+1, LEN(@stringToSplit)-@pos) END RETURN END

小开

这是基于 Andy Robertson 的答案，我需要一个逗号以外的分隔符。

CREATE FUNCTION dbo.splitstring ( @stringToSplit nvarchar(MAX), @delim nvarchar(max)) RETURNS @returnList TABLE ([value] [nvarchar] (MAX)) AS BEGIN DECLARE @value NVARCHAR(max) DECLARE @pos INT WHILE CHARINDEX(@delim, @stringToSplit) > 0 BEGIN SELECT @pos = CHARINDEX(@delim, @stringToSplit) SELECT @value = SUBSTRING(@stringToSplit, 1, @pos - 1) INSERT INTO @returnList SELECT @value SELECT @stringToSplit = SUBSTRING(@stringToSplit, @pos + LEN(@delim), LEN(@stringToSplit) - @pos) END INSERT INTO @returnList SELECT @stringToSplit RETURN END GO

使用它:

SELECT * FROM dbo.splitstring('test1 test2 test3', ' ');

(在 SQLServer2008R2上测试)

编辑: 正确的测试代码

小开

我采用 xml 路由，将值包装成元素(M，但任何元素都可以) :

declare @v nvarchar(max) = '100,201,abcde' select a.value('.', 'varchar(max)') from (select cast('<M>' + REPLACE(@v, ',', '</M><M>') + '</M>' AS XML) as col) as A CROSS APPLY A.col.nodes ('/M') AS Split(a)

小开

很简单

DECLARE @String varchar(100) = '11,21,84,85,87' SELECT * FROM TB_PAPEL WHERE CD_PAPEL IN (SELECT value FROM STRING_SPLIT(@String, ',')) -- EQUIVALENTE SELECT * FROM TB_PAPEL WHERE CD_PAPEL IN (11,21,84,85,87)

提交答案