在SQL Server中如何分割字符串

Question

我在数据库中有一个逗号分隔的字符串，例如'apple,banana,pineapple,grapes'。我需要根据逗号拆分这个字符串并进行迭代。由于Sql Server没有内置函数，因此是否有一种有效的方法可以实现这个目标。

- rampuriyaaa

---

你使用的是哪种服务器端语言？ - dev7

Answer 1

尝试这个函数

CREATE FUNCTION [dbo].[func_Split] 
    (   
    @DelimitedString    varchar(8000),
    @Delimiter              varchar(100) 
    )
RETURNS @tblArray TABLE
    (
    ElementID   int IDENTITY(1,1),  -- Array index
    Element     varchar(1000)               -- Array element contents
    )
AS
BEGIN

    -- Local Variable Declarations
    -- ---------------------------
    DECLARE @Index      smallint,
                    @Start      smallint,
                    @DelSize    smallint

    SET @DelSize = LEN(@Delimiter)

    -- Loop through source string and add elements to destination table array
    -- ----------------------------------------------------------------------
    WHILE LEN(@DelimitedString) > 0
    BEGIN

        SET @Index = CHARINDEX(@Delimiter, @DelimitedString)

        IF @Index = 0
            BEGIN

                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(@DelimitedString)))

                BREAK
            END
        ELSE
            BEGIN

                INSERT INTO
                    @tblArray 
                    (Element)
                VALUES
                    (LTRIM(RTRIM(SUBSTRING(@DelimitedString, 1,@Index - 1))))

                SET @Start = @Index + @DelSize
                SET @DelimitedString = SUBSTRING(@DelimitedString, @Start , LEN(@DelimitedString) - @Start + 1)

            END
    END

    RETURN
END

使用示例 - 只需将逗号分隔的字符串和所需的分隔符传递给该函数即可。

DECLARE @SQLStr varchar(100)
SELECT @SQLStr = 'Mickey Mouse, Goofy, Donald Duck, Pluto, Minnie Mouse'

SELECT
    *
FROM
    dbo.func_split(@SQLStr, ',')

结果会像这样：

- Vignesh Kumar A

---

我会使用 SET @DelSize = LEN(@Delimiter + 'x') - 1 来处理带有尾随空格的分隔符，例如 ' ' 和 ', '。 - Chry Cheng

非常感谢Vignesh Kumar，帮了大忙！ - SajithG

@ChryCheng - 这绝对是应该包含在这个解决方案中的内容。 - callisto

我知道这篇文章相当老了，但还是要谢谢。我唯一遇到的问题是当子字符串有转义分隔符时，例如 "Mickey,Mouse, Goofy..." - 这将把 "Mickey" 和 "Mouse" 分为两部分 - 但实际上只应该得到 "Mickey,Mouse" 作为一个字符串。 - Aleks G

Answer 2

...... 因为在 SQL Server 中没有内置函数 ......

您提问时这是正确的，但 SQL Server 2016 引入了 STRING_SPLIT。

所以您可以直接使用该函数。

SELECT value
FROM   STRING_SPLIT ('apple,banana,pineapple,grapes', ',') 

有一些限制（只接受单个字符的分隔符，缺乏任何列以指示拆分索引最为引人注目）。各种限制以及一些性能测试的有希望的结果在Aaron Bertrand的这篇博客文章中。

- Martin Smith

Answer 3

您可以通过将逗号替换为自定义标签（在本例中为<w>表示单词），来将数据转换为XML格式。

create table t(col varchar(255));
insert into t values ('apple,banana,pineapple,grapes');
insert into t values ('car,bike,airplane');

select cast(('<w>' + replace(col, ',', '</w><w>') + '</w>') as xml) as xmlValue
  from t

返回的结果

|                                               XMLVALUE |
|--------------------------------------------------------|
| <w>apple</w><w>banana</w><w>pineapple</w><w>grapes</w> |
|                   <w>car</w><w>bike</w><w>airplane</w> |

现在，如果将此查询用作内部xml选择，则外部查询可以将其拆分为不同的行:

解决方案:

select split.xmlTable.value('.', 'varchar(255)') as xmlValue
from (

   select cast(('<w>' + replace(col, ',', '</w><w>') + '</w>') as xml) as xmlValue
     from t

) as xmlTable
cross apply xmlValue.nodes ('/w') as split(xmlTable);

Live sqlFiddle

- Jose Rui Santos

Answer 4

我有一个使用递归的解决方案如下

Create function split_string(@str as nvarchar(max),@separator as char(1)) returns @myvalues Table (id int identity(1,1),myval nvarchar(100))
as 
--Kamel Gazzah
--23/04/2019
begin
with cte as(
select @str [mystr],
cast(1 as int) [Start],
charindex(@separator,@str)as Nd
union all
select substring(@str,nd+1,len(@str)),cast(Nd+1 as int),charindex(@separator,@str,Nd+1) from cte
where nd>0
)
insert into @myvalues(myval) 
select case when nd>0 then substring(@str,start,Nd-start) 
else substring(@str,start,len(@str)) end [splitted] 
from cte   OPTION (MAXRECURSION 1000);
return ;
end;

你可以调用这个函数。

select * from split_string('apple,banana,pineapple,grapes',',')

- Kemal AL GAZZAH