SQL 拆分sql server中的字符串
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21428612/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Splitting the string in sql server
提问by rampuriyaaa
I have a string in the database which is comma separated.Like 'apple,banana,pineapple,grapes' I need to split this string on the basis of comma and iterate through this.Since there is no built in function in sql server, Is there any efficient way in which this objective can be attained.
我在数据库中有一个逗号分隔的字符串。像'apple,banana,pineapple,grapes'我需要在逗号的基础上拆分这个字符串并遍历这个。因为在sql server中没有内置函数，是有任何有效的方法可以实现这一目标。
回答by Vignesh Kumar A
Try this function
试试这个功能
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
Example Usage– simply pass the function the comma delimited string as well as your required delimiter.
示例用法- 只需将逗号分隔的字符串以及所需的分隔符传递给函数。
DECLARE @SQLStr varchar(100)
SELECT @SQLStr = 'Mickey Mouse, Goofy, Donald Duck, Pluto, Minnie Mouse'
SELECT
*
FROM
dbo.func_split(@SQLStr, ',')
Result will be like this
结果会是这样
回答by Martin Smith
... Since there is no built in function in sql server ...
...由于sql server中没有内置函数...
That was true at the time you asked this question but SQL Server 2016 introduces STRING_SPLIT.
在您提出这个问题时确实如此，但 SQL Server 2016 引入了STRING_SPLIT.
So you can just use
所以你可以使用
SELECT value
FROM STRING_SPLIT ('apple,banana,pineapple,grapes', ',')
There are some limitations (only single character delimiters accepted and a lack of any column indicating the split index being the most eye catching). The various restrictions and some promising results of performance testing are in this blog post by Aaron Bertrand.
有一些限制（只接受单字符分隔符，并且没有任何列表明拆分索引最引人注目）。性能测试的各种限制和一些有希望的结果在Aaron Bertrand 的这篇博文中。
回答by Jose Rui Santos
You can convert your data to XML, by replacing the comma by a custom tag, in this case, <w>for word.
您可以将数据转换为 XML，方法是将逗号替换为自定义标记，在本例中<w>为 word。
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
Which returns
哪个返回
| XMLVALUE |
|--------------------------------------------------------|
| <w>apple</w><w>banana</w><w>pineapple</w><w>grapes</w> |
| <w>car</w><w>bike</w><w>airplane</w> |
Now, if you use this query as a inner xml select, the outer query can split it into distinct rows:
现在，如果您将此查询用作内部 xml 选择，则外部查询可以将其拆分为不同的行：
Solution:
解决方案：
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);
回答by Kemal AL GAZZAH
I have a solution using Recursion as follows
我有一个使用递归的解决方案如下
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;
You can call this function
你可以调用这个函数
select * from split_string('apple,banana,pineapple,grapes',',')
