与字符串串联相反-如何将字符串拆分为多个记录

可能的复制品:
在 SQL 中拆分字符串

我在 SQL 中见过 关于字符串连接的几个问题。 我想知道您如何处理相反的问题: 将昏迷分隔的字符串分割为数据行:

假设我有桌子:

userTypedTags(userID,commaSeparatedTags) 'one entry per user
tags(tagID,name)

并希望将数据插入到表中

userTag(userID,tagID) 'multiple entries per user

受 哪些标签不在数据库中？问题启发

剪辑

谢谢你的回答，实际上有更多的值得接受，但我只能选择一个，而且 卡德鲁提出的解决方案的递归对我来说似乎相当干净。它适用于 SQLServer2005及以上版本。

对于 SQLServer 的早期版本，可以使用解决方案 由米尔斯提供。 对于处理文本数据类型 Wcm 答案将是有帮助的。再次感谢。

181746 次浏览
小开

我使用此函数(SQLServer2005及以上版本)。

create function [dbo].[Split]
(
@string nvarchar(4000),
@delimiter nvarchar(10)
)
returns @table table
(
[Value] nvarchar(4000)
)
begin
declare @nextString nvarchar(4000)
declare @pos int, @nextPos int


set @nextString = ''
set @string = @string + @delimiter


set @pos = charindex(@delimiter, @string)
set @nextPos = 1
while (@pos <> 0)
begin
set @nextString = substring(@string, 1, @pos - 1)


insert into @table
(
[Value]
)
values
(
@nextString
)


set @string = substring(@string, @pos + len(@delimiter), len(@string))
set @nextPos = @pos
set @pos = charindex(@delimiter, @string)
end
return
end
小开

这是我前阵子写的。它假设分隔符是逗号，并且单个值不大于127个字符。它可以很容易地被修改。

它的好处是不限于4,000个字符。

祝你好运！

ALTER Function [dbo].[SplitStr] (
@txt text
)
Returns @tmp Table
(
value varchar(127)
)
as
BEGIN
declare @str varchar(8000)
, @Beg int
, @last int
, @size int


set @size=datalength(@txt)
set @Beg=1




set @str=substring(@txt,@Beg,8000)
IF len(@str)<8000 set @Beg=@size
ELSE BEGIN
set @last=charindex(',', reverse(@str))
set @str=substring(@txt,@Beg,8000-@last)
set @Beg=@Beg+8000-@last+1
END


declare @workingString varchar(25)
, @stringindex int






while @Beg<=@size Begin
WHILE LEN(@str) > 0 BEGIN
SELECT @StringIndex = CHARINDEX(',', @str)


SELECT
@workingString = CASE
WHEN @StringIndex > 0 THEN SUBSTRING(@str, 1, @StringIndex-1)
ELSE @str
END


INSERT INTO
@tmp(value)
VALUES
(cast(rtrim(ltrim(@workingString)) as varchar(127)))
SELECT @str = CASE
WHEN CHARINDEX(',', @str) > 0 THEN SUBSTRING(@str, @StringIndex+1, LEN(@str))
ELSE ''
END
END
set @str=substring(@txt,@Beg,8000)


if @Beg=@size set @Beg=@Beg+1
else IF len(@str)<8000 set @Beg=@size
ELSE BEGIN
set @last=charindex(',', reverse(@str))
set @str=substring(@txt,@Beg,8000-@last)
set @Beg=@Beg+8000-@last+1


END
END


return
END
小开 
SELECT substring(commaSeparatedTags,0,charindex(',',commaSeparatedTags))

会给你第一个标签。通过每次将 substring 和 charindex 组合在一个更深的层上，您可以以类似的方式获得第二个子串，以此类推。这是一个立竿见影的解决方案，但它只能使用很少的标记，因为查询的大小增长非常快，变得不可读。然后转移到函数上，正如本文其他更复杂的答案所概述的那样。

小开
最佳答案

解决这个问题有很多种方法，包括这个小宝石:

CREATE FUNCTION dbo.Split (@sep char(1), @s varchar(512))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1)
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s
FROM Pieces
)
小开

略微修改了上面的 解决方案，使其适用于可变长度的分隔符。

create FUNCTION dbo.fn_Split2 (@sep nvarchar(10), @s nvarchar(4000))
RETURNS table
AS
RETURN (
WITH Pieces(pn, start, stop) AS (
SELECT 1, 1, CHARINDEX(@sep, @s)
UNION ALL
SELECT pn + 1, stop + (datalength(@sep)/2), CHARINDEX(@sep, @s, stop + (datalength(@sep)/2))
FROM Pieces
WHERE stop > 0
)
SELECT pn,
SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS s
FROM Pieces
)

注意: 我使用 datallength ()是因为 len ()在有尾随空格时报告错误。

小开

您还可以使用 XML 就像这里看到的实现这种效果，它消除了所提供的答案的局限性，这些答案似乎都以某种方式包含递归。我在这里所做的特殊使用允许最多32个字符的分隔符，但是无论它需要多大，这个分隔符都可以增加。

create FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
SELECT r.value('.','VARCHAR(MAX)') as Item
FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(REPLACE(REPLACE(@s,'& ','&amp; '),'<','&lt;'), @sep, '</r><r>') + '</r></root>') as valxml) x
CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
)

然后，您可以使用:

SELECT * FROM dbo.Split(' ', 'I hate bunnies')

结果是:

-----------
|I        |
|---------|
|hate     |
|---------|
|bunnies  |
-----------

我应该注意到，我其实并不讨厌兔子... ... 只是因为某些原因，它突然出现在我的脑海里。
下面是我在内联表值函数中使用相同方法所能得到的最接近的结果。别用它，它效率太低了！只是为了参考。

CREATE FUNCTION [dbo].[Split] (@sep VARCHAR(32), @s VARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
SELECT r.value('.','VARCHAR(MAX)') as Item
FROM (SELECT CONVERT(XML, N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>') as valxml) x
CROSS APPLY x.valxml.nodes('//root/r') AS RECORDS(r)
)
小开

对于将字符串拆分为单词的特殊情况，我遇到了 SQLServer2008的另一种解决方案。

with testTable AS
(
SELECT 1 AS Id, N'how now brown cow' AS txt UNION ALL
SELECT 2, N'she sells sea shells upon the sea shore' UNION ALL
SELECT 3, N'red lorry yellow lorry' UNION ALL
SELECT 4, N'the quick brown fox jumped over the lazy dog'
)


SELECT display_term, COUNT(*) As Cnt
FROM testTable
CROSS APPLY sys.dm_fts_parser('"' + txt + '"', 1033, 0,0)
GROUP BY display_term
HAVING COUNT(*) > 1
ORDER BY Cnt DESC

报税表

display_term                   Cnt
------------------------------ -----------
the                            3
brown                          2
lorry                          2
sea                            2
小开

下面是与2005年以前的 SQLServer 版本兼容的 Split函数。

CREATE FUNCTION dbo.Split(@data nvarchar(4000), @delimiter nvarchar(100))
RETURNS @result table (Id int identity(1,1), Data nvarchar(4000))
AS
BEGIN
DECLARE @pos   INT
DECLARE @start INT
DECLARE @len   INT
DECLARE @end   INT


SET @len   = LEN('.' + @delimiter + '.') - 2
SET @end   = LEN(@data) + 1
SET @start = 1
SET @pos   = 0


WHILE (@pos < @end)
BEGIN
SET @pos = CHARINDEX(@delimiter, @data, @start)
IF (@pos = 0) SET @pos = @end


INSERT @result (data) SELECT SUBSTRING(@data, @start, @pos - @start)
SET @start = @pos + @len
END


RETURN
END
小开

使用 CLR，这里有一个简单得多的替代方案，在所有情况下都有效，但比公认的答案快40% :

using System;
using System.Collections;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;
using Microsoft.SqlServer.Server;


public class UDF
{
[SqlFunction(FillRowMethodName="FillRow")]
public static IEnumerable RegexSplit(SqlString s, SqlString delimiter)
{
return Regex.Split(s.Value, delimiter.Value);
}


public static void FillRow(object row, out SqlString str)
{
str = new SqlString((string) row);
}
}

当然，它仍然比 PostgreSQL 的 regexp_split_to_table慢8倍。

小开

我投票支持“内森惠勒”的答案，因为我发现“凯德鲁”的答案不工作以上的一定字符串大小。

有几点

我发现添加 DISTINCT 关键字改善了我的性能。

内森的回答只有当你的标识符是5个字符或更少时才有效，当然你可以调整... 如果你拆分的项目是 内景标识符，你可以像我一样，我们一样如下:

CREATE FUNCTION [dbo].Split
(
@sep VARCHAR(32),
@s VARCHAR(MAX)
)
RETURNS
@result TABLE (
Id INT NULL
)
AS
BEGIN
DECLARE @xml XML
SET @XML = N'<root><r>' + REPLACE(@s, @sep, '</r><r>') + '</r></root>'


INSERT INTO @result(Id)
SELECT DISTINCT r.value('.','int') as Item
FROM @xml.nodes('//root//r') AS RECORDS(r)


RETURN
END
小开

我通常使用以下代码进行此操作:

create function [dbo].[Split](@string varchar(max), @separator varchar(10))
returns @splited table ( stringPart varchar(max) )
with execute as caller
as
begin
declare @stringPart varchar(max);
set @stringPart = '';


while charindex(@separator, @string) > 0
begin
set @stringPart = substring(@string, 0, charindex(@separator, @string));
insert into @splited (stringPart) values (@stringPart);
set @string = substring(@string, charindex(@separator, @string) + len(@separator), len(@string) + 1);
end


return;
end
go

您可以通过以下查询对其进行测试:

declare @example varchar(max);
set @example = 'one;string;to;rule;them;all;;';


select * from [dbo].[Split](@example, ';');

提交答案