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
- Generate a set or sequence without loops – part 2
- Generate a set or sequence without loops – part 3
生成没有循环的集合或序列 - 第1部分
生成没有循环的集合或序列 - 第2部分
生成没有循环的集合或序列 - 第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
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
- Generate a set or sequence without loops – part 2
- Generate a set or sequence without loops – part 3
生成没有循环的集合或序列 - 第1部分
生成没有循环的集合或序列 - 第2部分
生成没有循环的集合或序列 - 第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