在SQL Server中使用逗号分割字符串

Please help how to split words in string with comma

请帮助如何用逗号分隔字符串中的单词

Eg: for 'abcdef'

例如:'abcdef'

Output : 'a,b,c,d,e,f'

输出:'a,b,c,d,e,f'

7 个解决方案

#1

If you want to operate on a tsql variable:

如果要对tsql变量进行操作:

DECLARE @str VARCHAR(40) = 'abcdef'

you can use the following code that uses spt_values to create a tally and STUFF to inject , characters in the string:

您可以使用以下代码使用spt_values创建一个计数器和STUFF来注入,字符串中的字符:

SELECT @str = STUFF(@str, Number * 2, 0, ',')
FROM [master].[dbo].[spt_values]
WHERE Type = 'P' AND
      Number BETWEEN 1 AND LEN(@str) - 1

After the execution of the above, @str is a,b,c,d,e,f.

执行上述操作后,@ str为a,b,c,d,e,f。

#2

This is really not best handled in pure SQL and would be more suited to an application tier, or CLR, but one solution would be to split out your string into its individual components, then rebuild it with a comma separator. To do this first you need a numbers table, the following series goes into some depth about the best way of doing this:

这在纯SQL中并不是最好处理的,并且更适合应用程序层或CLR,但一种解决方案是将字符串拆分为单独的组件,然后使用逗号分隔符重建它。要首先执行此操作,您需要一个数字表,以下系列深入探讨了执行此操作的最佳方法:

Generate a set or sequence without loops – part 1

生成没有循环的集合或序列 - 第1部分

Generate a set or sequence without loops – part 2

生成没有循环的集合或序列 - 第2部分

Generate a set or sequence without loops – part 3

生成没有循环的集合或序列 - 第3部分

For the sake of the answer I will assume you have no numbers table, and need to create one on the fly, and the most efficient way to do this is using stacked CTEs. The following will return a list from 1 to 10000:

为了答案,我假设您没有数字表,需要动态创建一个,最有效的方法是使用堆叠CTE。以下将返回从1到10000的列表:

WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) FROM N3)
SELECT  *
FROM    Numbers;

Then you can use these numbers to split your string:

然后你可以使用这些数字来分割你的字符串:

DECLARE @T TABLE (Col VARCHAR(10));
INSERT @T VALUES ('abcdef');

WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) FROM N3)

SELECT  *,
        Letter = SUBSTRING(t.Col, n.Number, 1)
FROM    @T AS t
        INNER JOIN Numbers n
            ON n.Number <= LEN(t.Col);

This gives you:

这给你:

Col     Number  Letter
------------------------
abcdef  1       a
abcdef  2       b
abcdef  3       c
abcdef  4       d
abcdef  5       e
abcdef  6       f

Then you can rebuild your string using SQL Servers XML extensions:

然后,您可以使用SQL Servers XML扩展重建字符串:

DECLARE @T TABLE (Col VARCHAR(10));
INSERT @T VALUES ('abcdef'), ('test');

WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) FROM N3)

SELECT  t.Col,
        Split = (   SELECT  CASE WHEN n.Number = 1 THEN '' ELSE ',' END + SUBSTRING(t2.Col, n.Number, 1)
                    FROM    @T AS t2
                            INNER JOIN Numbers n
                                ON n.Number <= LEN(t2.Col)
                    WHERE   t2.Col = t.Col
                    FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)')
FROM    @T AS t;

The benefit of this method is that you can use it as an inline table valued function:

此方法的好处是您可以将其用作内联表值函数:

CREATE FUNCTION dbo.InjectDelimiter (@String VARCHAR(1000), @Delimiter CHAR(1))
RETURNS TABLE 
AS
RETURN 
(   WITH N1 AS (SELECT N FROM (VALUES(1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) n (N)),
    N2 (N) AS (SELECT 1 FROM N1 AS N1 CROSS JOIN N1 AS N2),
    N3 (N) AS (SELECT 1 FROM N2 AS N1 CROSS JOIN N2 AS N2),
    Numbers (Number) AS (SELECT ROW_NUMBER() OVER(ORDER BY N) FROM N3)
    SELECT  Split = (   SELECT  CASE WHEN n.Number = 1 THEN '' ELSE @Delimiter END + SUBSTRING(@String, n.Number, 1)
                        FROM    Numbers n
                        WHERE   n.Number <= LEN(@String)
                        FOR XML PATH(''), TYPE
                    ).value('.', 'NVARCHAR(MAX)')
);

Then you can call it as:

然后你可以把它称为:

SELECT  t.Name, i.Split
FROM    sys.tables AS t
        CROSS APPLY dbo.InjectDelimiter(t.name, ',') AS i;

Which will perform much better than a scalar function if you need to call it on multiple rows.

如果你需要在多行上调用它,它的性能会比标量函数好得多。

#3

I would use a while loop:

我会使用while循环:

DECLARE @str VARCHAR(max) = 'abcdef'

DECLARE @loop INT = LEN(@str)

WHILE @loop > 1
  SELECT @str = STUFF(@str, @loop, 0, ','), @loop -= 1

SELECT @str

#4

You can create the following function for this

您可以为此创建以下功能

CREATE FUNCTION PutCommasBetweenChars 

   (@String VARCHAR(100))

RETURNS VARCHAR(100)
AS
BEGIN
   DECLARE @pos INT, @result VARCHAR(100); 
   SET @result = @String; 
   SET @pos = 2 -- location where we want first space 
   WHILE @pos < LEN(@result)+1 
   BEGIN 
       SET @result = STUFF(@result, @pos, 0, ','); 
       SET @pos = @pos+2; 
   END 
   RETURN @result; 
END
GO

And execute it as follows

并按如下方式执行

print dbo.PutCommasBetweenChars('abcdef')

#5

A time ago I came up with this solution :

前一段时间我想出了这个解决方案:



Declare @separator as nvarchar(1)= ','
Declare @filterlist as nvarchar(MAX) = '1,2,3,4,5'

IF OBJECT_ID('tempdb..#filterList') IS NOT NULL
DROP TABLE #filterlist

--Create temporary filter list table
create table #FilterList (
filter varchar(100) Not NULL
)

--Add 1 comma to the filter list, used for processing the list
set @filterlist = @filterList + @separator 

--Declare and set default variable values for processing Filter list
DECLARE @pos INT
DECLARE @len INT
DECLARE @value varchar(100)

set @pos = 0
set @len = 0

--Loop thru the string of filter list, separate the , values and insert into the #Filterlist
       WHILE CHARINDEX(@separator , @filterList, @pos+1)>0

    BEGIN

        set @len = CHARINDEX(@separator , @filterList, @pos+1) - @pos
        set @value = SUBSTRING(@filterList, @pos, @len)

        insert Into #FilterList (filter) Values(@value)

        set @pos = CHARINDEX(@separator , @filterList, @pos+@len) + 1

    END

    select * from #FilterList

#6

After spend some time I have found your solutions

花了一些时间后,我找到了你的解决方案

DECLARE @Chars VARCHAR(8000) 

SELECT  @Chars = COALESCE(@Names + ', ', '') + Main.SplitChar
FROM 
(

select (substring(a.b, v.number+1, 1)) AS SplitChar
from (select 'QWERTY' b) a
join master..spt_values v on v.number < len(a.b)
where v.type = 'P'
) AS MaIn

SELECT @Chars

Please let me know, is it working or not. :)

请让我知道,它是否有效。 :)

#7

Thank you all ! I tried below code, but I got better answers from you all guys

CREATE TABLE #TEMP (ID INT IDENTITY, CHARR VARCHAR(MAX))
DECLARE @DATA NVARCHAR(MAX)='ABCDEFG'

DECLARE @LEN INT = LEN(@DATA)
DECLARE @INT INT = 1
WHILE @INT<=@LEN
BEGIN 
INSERT INTO #TEMP VALUES (SUBSTRING(@DATA,@INT,1))
SET @INT=@INT+1
end

SELECT STUFF(
(SELECT ',' + S.CHARR
FROM #TEMP s
ORDER BY s.ID
FOR XML PATH('')),1,1,'') AS CSV

#1