问答中心分类: SQL如何拆分带分隔符的字符串,以便访问单个项目?
0
匿名用户 提问 3月 前

使用SQL Server,如何拆分字符串以访问项目x?
拿一个字符串“你好,约翰·史密斯”。如何按空格分割字符串并访问索引1处的项,该项应返回“John”?

29 Answers
0
Nathan Bedford 回答 3月 前

我不相信SQL Server有内置的拆分函数,所以除了UDF之外,我知道的唯一其他答案是劫持PARSENAME函数:

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)

PARSENAME接受一个字符串并在句点字符上拆分它。它将一个数字作为其第二个参数,该数字指定要返回的字符串段(从后向前)。

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3)  --return Hello

明显的问题是当字符串已经包含句点时。我仍然认为使用自定义项是最好的方式……还有其他建议吗?

Nathan Bedford 回复 3月 前

谢谢索尔……我应该指出,这个解决方案对于真正的开发来说确实是一个糟糕的解决方案。PARSENAME只需要四个部分,因此使用包含四个以上部分的字符串会导致返回NULL。UDF解决方案明显更好。

Factor Mystic 回复 3月 前

这是一个伟大的黑客,也让我哭泣,像这样的东西是必要的,在真正的语言这么简单。

NothingsImpossible 回复 3月 前

为了使索引以“正确”的方式工作,即从1开始,我用REVERSE劫持了您的劫持:REVERSE(PARSENAME(REPLACE(REVERSE(’Hello John Smith’),”,.’),1) )–返回Hello

Bacon Bits 回复 3月 前

@因子系统第一范式要求不要在单个字段中放置多个值。这实际上是关系数据库管理系统的第一条规则。A.SPLIT()未提供函数,因为它会导致数据库设计不佳,并且数据库永远不会优化为使用以这种格式存储的数据。RDBMS没有义务帮助开发人员做它设计的愚蠢的事情处理。正确答案将总是“像我们40年前告诉你的那样规范化你的数据库。”SQL和RDBMS都不是设计糟糕的罪魁祸首。

MrBliz 回复 3月 前

这很酷。碰巧我处理的数据有四个元素

hello_earth 回复 3月 前

+1总是可以为>4个令牌组合反向()、charindex()和子字符串()来编造一个快速而肮脏的工作解决方案

Tim Abell 回复 3月 前

@BaconBits虽然我在理论上同意,但在实践中,这样的工具在规范化之前的人所做的糟糕设计时很有用。

wwmbes 回复 3月 前

当字符串中的拆分数量从左到右变化时,那么@NothingsImpossible路由就是一条可行之路。

wwmbes 回复 3月 前

@反转的性能(PARSENAME(REPLACE(REVERSE(’Hello John Smith’),”,”),1) )解决方案非常好,4个字段限制适合我的问题。谢谢

wwmbes 回复 3月 前

@你好,地球,我想看看你有什么想法。请给我们看看。

hello_earth 回复 3月 前

@我贴了一个单独的答案——它很快,又脏又丑,来源于同样的原则,但无论如何,既然你问了。干杯

Kyle Weller 回复 3月 前

意识:由于parsename函数旨在返回数据库标识符,因此返回的值限制为128个字符(一种sysname数据类型,对应于nvarchar(128))。如果超出此范围,将返回NULL。

aKiRa 回复 3月 前

对于意识:它只适用于四个元素或更少。如果有四个以上的元素,则PARSENAME始终返回NULL,即使索引小于5。docs.microsoft.com/it-it/sql/t-sql/functions/…

0
vzczc 回答 3月 前

首先,创建一个函数(使用CTE,公共表表达式不需要临时表)

create function dbo.SplitString 
    (
        @str nvarchar(4000), 
        @separator char(1)
    )
    returns table
    AS
    return (
        with tokens(p, a, b) AS (
            select 
                1, 
                1, 
                charindex(@separator, @str)
            union all
            select
                p + 1, 
                b + 1, 
                charindex(@separator, @str, b + 1)
            from tokens
            where b > 0
        )
        select
            p-1 zeroBasedOccurance,
            substring(
                @str, 
                a, 
                case when b > 0 then b-a ELSE 4000 end) 
            AS s
        from tokens
      )
    GO

然后,像这样将其用作任何表(或将其修改为适合现有存储过程)。

select s 
from dbo.SplitString('Hello John Smith', ' ')
where zeroBasedOccurance=1

使现代化
对于长度超过4000个字符的输入字符串,以前的版本将失败。此版本考虑了以下限制:

create function dbo.SplitString 
(
    @str nvarchar(max), 
    @separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
    select 
        cast(1 as bigint), 
        cast(1 as bigint), 
        charindex(@separator, @str)
    union all
    select
        p + 1, 
        b + 1, 
        charindex(@separator, @str, b + 1)
    from tokens
    where b > 0
)
select
    p-1 ItemIndex,
    substring(
        @str, 
        a, 
        case when b > 0 then b-a ELSE LEN(@str) end) 
    AS s
from tokens
);

GO

用法保持不变。

Pking 回复 3月 前

它很优雅,但由于递归深度的限制,只适用于100个元素。

Michał Powaga 回复 3月 前

@Pking,不,默认为100(防止无限循环)。使用MAXRECURSION提示定义递归级别数(032767,0是“无限制”-可能会压碎服务器)。顺便说一句,答案比PARSENAME,因为它是通用的:-)+1.

Michał Powaga 回复 3月 前

正在添加maxrecursion要解决这个问题,请记住这个问题及其答案如何设置maxrecursion表值函数内的CTE选项.

AHiggins 回复 3月 前

具体来说,参考Crisfole的答案-他的方法稍微放慢了速度,但比大多数其他方法更简单。

Tim Abell 回复 3月 前

次要的一点是,因为更改了列名,所以用法不一样s不再定义

0
Aaron Bertrand 回答 3月 前

这里的大多数解决方案使用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你想把它作为函数的参数,但我将把它作为练习留给读者。
你不能这样做只是这个出生地的STRING_SPLIT作用添加到SQL Server 2016中,因为无法保证输出将按原始列表的顺序呈现。换句话说,如果你过去3,6,1结果可能是这样的,但是能够1,3,6.我已经请求社区帮助改进这里的内置功能:

有足够的质量的反馈,他们实际上可能会考虑进行一些增强:

更多关于拆分函数的信息,为什么(并证明)循环和递归连接时序分类不能扩展,以及更好的替代方法,如果拆分来自应用层的字符串:

不过,在SQL Server 2016或更高版本上,您应该看看STRING_SPLIT()STRING_AGG():

T-moty 回复 3月 前

最好的答案,伊姆霍。在其他一些答案中,存在SQL递归限制为100的问题,但在本例中并非如此。非常快速且非常简单的实现。+2按钮在哪里?

Mikhail Boyarsky 回复 3月 前

感谢亚伦;但是有人能解释为什么我通过了吗varchar(不是varchar(max))作为参数,此函数返回空列表?喜欢declare @list varchar = 'something'; select from dbo.SplitString(@list, ';');

Aaron Bertrand 回复 3月 前

@米哈伊尔因为varchar没有长度可以是varchar(30)varchar(1)取决于上下文。不要试图理解那个问题-只是不要使用那种语法。曾经.

wwmbes 回复 3月 前

我用以下用法逐字尝试了此函数:select * from DBO.SplitString('Hello John smith', ' ');产生的产量为:价值你好,ello llo lo o John ohn hn smith mith th th h

Aaron Bertrand 回复 3月 前

@WWMBE尝试使用空格以外的分隔符。许多函数都会出现尾部空格被删除的问题。

wwmbes 回复 3月 前

@AaronBertrand GateKiller发布的原始问题涉及一个空格分隔符。

Alasdair C-S 回复 3月 前

“len”函数有一个隐式内置的“rtrim”,它来自80年代,当时SQL只有char(而不是varchar),因此请尝试以下方法:select len( ' ' ) as len_space, len( '* ' ) as len_star_space, len( ' *' ) as len_space_star, datalength( ' ' ) as datalength_space, datalength( '* ' ) as datalength_star_space, datalength( ' *' ) as datalength_space_star, case when '' = ' ' then 'yes' else 'no' end as nospace_equals_space修复:将“len”替换为“datalength”(但注意n(var)char)

user1255933 回复 3月 前

这会分割字符串。如何检索特定的第n个元素?

Aaron Bertrand 回复 3月 前

@用户1255933已寻址。

user1255933 回复 3月 前

@AaronBertrand非常感谢,我可以复制这个。

user1255933 回复 3月 前

@AaronBertrand添加了应用于整个表的示例:从mytable中选择o.myfield,u.value o交叉应用dbo.SplitString(o.myfield,“;”)u,其中u.idx=4

Michael 回复 3月 前

如果你没有“创建函数”权限,这将不起作用。。。

Aaron Bertrand 回复 3月 前

@迈克尔是的,这是真的。如果没有ALTER SCHEMA权限,您也不会有表可供选择,如果没有select权限,您将无法从中进行选择询问某人为您创建函数。或者在可以创建它的地方创建它(即使是暂时的,比如在tempdb中)。2016年以后,你应该使用STRING_SPLIT(),而不是你自己创建的函数。

Michael 回复 3月 前

@AaronBertrand我在2016年,但显然不兼容级别>=130(db管理员不确定,直到稍后才能调查)

Reversed Engineer 回复 3月 前

@AaronBertrand“在2016年+你应该使用STRING\u SPLIT()”:我无法想象STRING\U SPLIT的任何实际用途,因为它不会返回被拆分字符串部分的索引,并且根据这是文件,“输出顺序可能会有所不同,因为不能保证顺序与输入字符串中子字符串的顺序匹配”。

Reversed Engineer 回复 3月 前

我喜欢这样此Azure反馈项他说:“STRING\u SPLIT的功能还不完整”,“很遗憾这只是一个“建议”。实际上,它应该被列为一个“bug”,因为只有相对较小的用例集,其中元素结果集的枚举并不重要

Aaron Bertrand 回复 3月 前

@ReversedEngine有很多用法不(也不应该)关心列表的原始顺序。“找到列表中的所有客户。”如果列表是3,5,2,88,3,5,2? 我认为这是一个相当模糊的边缘案例,要求是“找到此列表中的所有客户”按此顺序渲染。“(我并不是反对修正这一点,我只是建议,我对该函数的大多数用法的印象与你的不同。这是一些公认答案中的轶事证据,这些答案显示了没有序号out的函数。)

Aaron Bertrand 回复 3月 前

@无论如何,ReversedEnginer更新了我的答案我对此的一些想法此外,顺便说一句,我是推动文档清晰性的人之一,输出顺序可能与输入顺序不匹配。

Reversed Engineer 回复 3月 前

@AaronBertrand“此外,作为旁白,我是推动文档清晰的人之一,输出顺序可能与输入顺序不匹配。”-非常感谢。我很感激你所做的“敦促微软改进”,以及帮助清理这种模糊的“微软傻瓜文档”。我也明白你的观点,当顺序无关紧要时,STRING\u SPLIT确实是一个很好的解决方案。

Josef B. 回复 3月 前

@AaronBertrand非常感谢您分享您的解决方案!希望你不介意我在这里发布我的修改,包括idx参数。顺便说一句,我对你提出的改进STRIN_SPLIT的要求投了票

Mattias W 回复 3月 前

@AaronBertrand您的解决方案可行,但您应该补充一点,人们应该使用聚集/非聚集索引理货表(数字序列表)。您使用的是sql server内置表,然后动态执行row\u number,这使得代码在中可用,而无需先创建计数表,但如果不必为函数的每次调用执行row\ u number,则性能会有很大提高。

Aaron Bertrand 回复 3月 前

@MattiasW我已经谈论了很多数字表(参见在这里具体来说,在这里). 在堆栈溢出问题上,我反复遇到的挑战是,提出问题的人很挑剔如此频繁回答“添加”一张桌子?你疯了吗?“所以我默认停止提供它。使用sys.all_objects无论如何,这将使您很难实际演示比数字表更糟糕的性能。

0
nathan_jr 回答 3月 前

您可以利用数字表来进行字符串解析。
创建物理数字表:

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]

这里的性能并不令人惊讶,但在一百万行表上调用函数并不是最好的主意。如果在多行上执行字符串拆分,我将避免使用该函数。

Pking 回复 3月 前

最好的解决方案是IMO,其他的有一些限制。这很快,可以解析包含许多元素的长字符串。

为什么按降序排列n?如果有三个项目,我们从1开始编号,那么第一个项目将是数字3,最后一个将是数字1。如果desc是否已删除?

nathan_jr 回复 3月 前

同意,将在asc方向更直观。我遵循使用desc的parsename()约定

Tim Abell 回复 3月 前

如果能解释一下它是如何工作的,那就太好了

wwmbes 回复 3月 前

在对多达3个字段的1亿行的测试中,ufn_ParseArray在25分钟后没有完成,而REVERSE(PARSENAME(REPLACE(REVERSE('Hello John Smith'), ' ', '.'), 1))在1.5分钟内完成“无事不可能”@hello\u earth您的解决方案在具有4个以上字段的较长字符串上比较如何?

wwmbes 回复 3月 前

进一步研究表明,当@NothingSublible版本嵌入到函数中并从那里使用时,它的性能比直接在查询中使用时差25倍。有人能评论一下原因吗?

nathan_jr 回复 3月 前

@WWMBE尝试使用具有聚集索引的物理数字表。主..spt\U值的使用仅用于说明

nathan_jr 回复 3月 前

@wwmbes添加了一个物理数字表示例

0
Shnugo 回答 3月 前

这个问题是不是关于字符串分割方法,但大约如何获取第n个元素.
这里的所有答案都是使用递归进行某种字符串拆分,CTEs、 多个CHARINDEX,REVERSEPATINDEX,发明函数,调用CLR方法,数字表,CROSS APPLY大多数答案涵盖了许多代码行。
但是-如果你真的只需要一种获取第n个元素的方法-这可以通过以下方式实现:真正的一行,没有自定义项,甚至没有子选择…作为一个额外的好处:类型安全
获取由空格分隔的第2部分:

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

当然可以使用变量对于分隔符和位置(使用sql:column要直接从查询的值检索位置,请执行以下操作:

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

如果您的字符串可能包括禁止的字符(尤其是其中一个&><),你仍然可以这样做。只需使用FOR XML PATH首先在字符串上隐式地用合适的转义序列替换所有禁止的字符。
这是一个非常特殊的情况,如果-另外-您的分隔符是分号。在本例中,我首先将分隔符替换为“#DLMT#”,最后将其替换为XML标记:

SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

SQL Server 2016更新+
遗憾的是,开发人员忘记返回部件的索引STRING_SPLIT但是,使用SQL Server 2016+,有JSON_VALUEOPENJSON.
具有JSON_VALUE我们可以将该位置作为索引数组传递。
对于OPENJSON这个文档明确指出:

当OPENJSON解析JSON数组时,该函数将JSON文本中元素的索引作为键返回。

一个类似字符串的1,2,3只需要括号:[1,2,3].
一串单词,如this is an example需要["this","is","an","example"].
这些是非常简单的字符串操作。试一试:

DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;

--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

–有关位置安全字符串拆分器,请参见此(基于零的):

SELECT  JsonArray.[key] AS [Position]
       ,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

在里面此帖子我测试了各种方法,发现OPENJSON真的很快。甚至比著名的“delimitedSplit8k()”方法快得多。。。
更新2-获取安全类型的值
我们可以使用阵列中的阵列只需使用加倍[[]]。这允许键入WITH-条款:

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|1|20190920';

DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');

SELECT @SomeDelimitedString          AS TheOriginal
      ,@JsonArray                    AS TransformedToJSON
      ,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment  VARCHAR(100) '$[0]'
    ,TheSecondFragment INT          '$[1]'
    ,TheThirdFragment  DATE         '$[2]') ValuesFromTheArray
Salman A 回复 3月 前

回复:如果你的字符串可能包含禁止的字符……你可以简单地像这样包装子字符串<x><![CDATA[x<&>x]]></x>.

Shnugo 回复 3月 前

@SalmanA是的,CDATA-各部分也可以处理这个问题……但在演员阵容结束后,他们就消失了(改为逃脱)text()隐含地)。我不喜欢引擎盖下的魔术,所以我更喜欢(SELECT 'Text with <&>' AS [*] FOR XML PATH(''))-方法。对我来说,这看起来更干净,无论如何都会发生…(更多关于CDATA和XML).