使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
建立在@NothingsImpossible解决方案上,或者,更确切地说,评论投票最多的答案(就在接受的答案下面),我发现下面的快速和肮脏的解决方案满足了我自己的需求-它的好处是完全在SQL域内。
给定一个字符串"first;second;third;fourth;fifth",比如说,我想获取第三个标记。只有当我们知道字符串将有多少标记时,这才有效——在这种情况下,它是5。所以我的操作方式是将最后两个令牌切掉(内部查询),然后将前两个令牌切掉(外部查询)
我知道这是丑陋的,涵盖了我所处的具体情况,但我张贴它只是为了以防有人发现它有用。干杯
select
REVERSE(
SUBSTRING(
reverse_substring,
0,
CHARINDEX(';', reverse_substring)
)
)
from
(
select
msg,
SUBSTRING(
REVERSE(msg),
CHARINDEX(
';',
REVERSE(msg),
CHARINDEX(
';',
REVERSE(msg)
)+1
)+1,
1000
) reverse_substring
from
(
select 'first;second;third;fourth;fifth' msg
) a
) b
其他回答
我知道这是一个老问题,但我认为有人可以从我的解决方案中受益。
select
SUBSTRING(column_name,1,CHARINDEX(' ',column_name,1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
,1
,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)+1
,LEN(column_name))
from table_name
SQL小提琴
优点:
它用' '分隔所有3个子字符串。 不能使用while循环,因为它会降低性能。 不需要枢轴,因为所有的结果子字符串将显示在 一行
限制:
一个人必须知道绝对的不。Of Spaces(子字符串)。
注:解决方案可以给出最多N个子字符串。
为了克服这个限制,我们可以使用下面的参考。
但是上面的解决方案不能在表中使用(实际上我不能使用它)。
我希望这个解决方案能帮助到一些人。
更新:在记录> 50000的情况下,不建议使用LOOPS,因为它会降低性能
可以利用Number表进行字符串解析。
创建一个物理数字表:
create table dbo.Numbers (N int primary key);
insert into dbo.Numbers
select top 1000 row_number() over(order by number) from master..spt_values
go
创建具有1000000行的测试表
create table #yak (i int identity(1,1) primary key, array varchar(50))
insert into #yak(array)
select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn
go
创建函数
create function [dbo].[ufn_ParseArray]
( @Input nvarchar(4000),
@Delimiter char(1) = ',',
@BaseIdent int
)
returns table as
return
( select row_number() over (order by n asc) + (@BaseIdent - 1) [i],
substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
from dbo.Numbers
where n <= convert(int, len(@Input)) and
substring(@Delimiter + @Input, n, 1) = @Delimiter
)
go
使用情况(在我的笔记本电脑上40秒内输出3mil行)
select *
from #yak
cross apply dbo.ufn_ParseArray(array, ',', 1)
清理
drop table dbo.Numbers;
drop function [dbo].[ufn_ParseArray]
这里的性能并不惊人,但在100万行表上调用函数并不是最好的主意。如果将字符串拆分到多行,我会避免使用该函数。
这里的大多数解决方案使用while循环或递归cte。我保证,如果你可以使用空格以外的分隔符,基于集合的方法会更好:
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM
(
SELECT n = Number,
[Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim)) = @Delim
) AS y
);
示例用法:
SELECT Value FROM dbo.SplitString('foo,bar,blat,foo,splunge',',')
WHERE idx = 3;
结果:
----
blat
您还可以将需要的idx作为参数添加到函数中,但我将把它作为练习留给读者。
您不能仅使用SQL Server 2016中添加的本地STRING_SPLIT函数来实现这一点,因为不能保证输出将按照原始列表的顺序呈现。换句话说,如果你传递3,6,1结果可能是这个顺序,但它可能是1,3,6。我已经在这里请求社区的帮助来改进内置功能:
请帮助改进STRING_SPLIT
有了足够的定性反馈,他们可能会考虑做出以下改进:
STRING_SPLIT不是特性完整的
更多关于拆分函数,为什么(和证明)while循环和递归cte不能扩展,以及更好的替代方案,如果拆分字符串来自应用层:
以正确的方式拆分字符串-或者退而求其次的方式 拆分字符串:后续 分割字符串:现在使用更少的T-SQL 比较字符串分割/连接方法 处理一个整数列表:我的方法 拆分整数列表:另一个汇总 更多关于拆分列表的内容:自定义分隔符、防止重复和维护顺序 在SQL Server中删除字符串中的重复项
在SQL Server 2016或更高版本上,你应该看看STRING_SPLIT()和STRING_AGG():
性能惊喜和假设:STRING_SPLIT() STRING_SPLIT()在SQL Server 2016:后续#1 STRING_SPLIT()在SQL Server 2016:后续#2 SQL Server v.Next: STRING_AGG()性能 使用SQL Server的新STRING_AGG和STRING_SPLIT函数解决老问题
使用SQL Server 2016及以上版本。使用这段代码修剪字符串,忽略NULL值,并按正确的顺序应用行索引。它也适用于空格分隔符:
DECLARE @STRING_VALUE NVARCHAR(MAX) = 'one, two,,three, four, five'
SELECT ROW_NUMBER() OVER (ORDER BY R.[index]) [index], R.[value] FROM
(
SELECT
1 [index], NULLIF(TRIM([value]), '') [value] FROM STRING_SPLIT(@STRING_VALUE, ',') T
WHERE
NULLIF(TRIM([value]), '') IS NOT NULL
) R
一个简单的优化算法:
ALTER FUNCTION [dbo].[Split]( @Text NVARCHAR(200),@Splitor CHAR(1) )
RETURNS @Result TABLE ( value NVARCHAR(50))
AS
BEGIN
DECLARE @PathInd INT
Set @Text+=@Splitor
WHILE LEN(@Text) > 0
BEGIN
SET @PathInd=PATINDEX('%'+@Splitor+'%',@Text)
INSERT INTO @Result VALUES(SUBSTRING(@Text, 0, @PathInd))
SET @Text= SUBSTRING(@Text, @PathInd+1, LEN(@Text))
END
RETURN
END