如何分割带分隔符的字符串，以便可以访问单个项?

使用SQL Server，我如何分割一个字符串，以便我可以访问项目x?

拿一根“你好，约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?

当前回答

Aaron Bertrand的回答很好，但也有缺陷。它不能准确地将空格作为分隔符处理(就像最初问题中的示例一样)，因为长度函数将空格带在后面。

下面是他的代码，稍微调整了一下，允许使用空格分隔符:

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(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_objects) AS x
          WHERE Number <= LEN(@List)
          AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim+'x')-1) = @Delim
      ) AS y
    );

2018-03-22 14:38:24

其他回答

你可以在SQL中拆分字符串，而不需要函数:

DECLARE @bla varchar(MAX)
SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C'

-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'varchar(36)') AS val 
FROM 
(
    SELECT 
    CAST('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

如果需要支持任意字符串(带有xml特殊字符)

DECLARE @bla NVARCHAR(MAX)
SET @bla = '<html>unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"<html>,Barnes & Noble,abc,def,ghi'

-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT 
    x.XmlCol.value('.', 'nvarchar(MAX)') AS val 
FROM 
(
    SELECT 
    CAST('<e>' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b 
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

2015-10-23 10:07:55

I realize this is a really old question, but starting with SQL Server 2016 there are functions for parsing JSON data that can be used to specifically address the OP's question--and without splitting strings or resorting to a user-defined function. To access an item at a particular index of a delimited string, use the JSON_VALUE function. Properly formatted JSON data is required, however: strings must be enclosed in double quotes " and the delimiter must be a comma ,, with the entire string enclosed in square brackets [].

DECLARE @SampleString NVARCHAR(MAX) = '"Hello John Smith"';
--Format as JSON data.
SET @SampleString = '[' + REPLACE(@SampleString, ' ', '","') + ']';
SELECT 
    JSON_VALUE(@SampleString, '$[0]') AS Element1Value,
    JSON_VALUE(@SampleString, '$[1]') AS Element2Value,
    JSON_VALUE(@SampleString, '$[2]') AS Element3Value;

输出

Element1Value         Element2Value       Element3Value
--------------------- ------------------- ------------------------------
Hello                 John                Smith

(1 row affected)

2019-05-07 02:54:28

使用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

2019-11-02 00:35:02

我开发了这个，

declare @x nvarchar(Max) = 'ali.veli.deli.';
declare @item nvarchar(Max);
declare @splitter char='.';

while CHARINDEX(@splitter,@x) != 0
begin
    set @item = LEFT(@x,CHARINDEX(@splitter,@x))
    set @x    = RIGHT(@x,len(@x)-len(@item) )
     select @item as item, @x as x;
end

你唯一应该注意的是。'。那@x的末尾就应该在这里。

2015-10-15 10:50:09

可以利用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万行表上调用函数并不是最好的主意。如果将字符串拆分到多行，我会避免使用该函数。

2008-10-27 16:48:51

如何分割带分隔符的字符串，以便可以访问单个项?

推荐文章

最新文章

标签