使用SQL Server，我如何分割一个字符串，以便我可以访问项目x?
拿一根“你好，约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
几乎所有其他答案都是替换正在分割的字符串，这浪费了CPU周期并执行不必要的内存分配。
我在这里介绍了一种更好的进行字符串拆分的方法:http://www.digitalruby.com/split-string-sql-server/
代码如下:
SET NOCOUNT ON
-- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = 'your|string|to|split|here'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = '|'
DECLARE @SplitStartPos int = 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
WHILE @SplitEndPos > 0
BEGIN
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
INSERT @SplitStringTable (Value) VALUES (@SplitValue)
SET @SplitStartPos = @SplitEndPos + 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)
SET NOCOUNT OFF
-- You can select or join with the values in @SplitStringTable at this point.
其他回答
试试这个:
CREATE function [SplitWordList]
(
@list varchar(8000)
)
returns @t table
(
Word varchar(50) not null,
Position int identity(1,1) not null
)
as begin
declare
@pos int,
@lpos int,
@item varchar(100),
@ignore varchar(100),
@dl int,
@a1 int,
@a2 int,
@z1 int,
@z2 int,
@n1 int,
@n2 int,
@c varchar(1),
@a smallint
select
@a1 = ascii('a'),
@a2 = ascii('A'),
@z1 = ascii('z'),
@z2 = ascii('Z'),
@n1 = ascii('0'),
@n2 = ascii('9')
set @ignore = '''"'
set @pos = 1
set @dl = datalength(@list)
set @lpos = 1
set @item = ''
while (@pos <= @dl) begin
set @c = substring(@list, @pos, 1)
if (@ignore not like '%' + @c + '%') begin
set @a = ascii(@c)
if ((@a >= @a1) and (@a <= @z1))
or ((@a >= @a2) and (@a <= @z2))
or ((@a >= @n1) and (@a <= @n2))
begin
set @item = @item + @c
end else if (@item > '') begin
insert into @t values (@item)
set @item = ''
end
end
set @pos = @pos + 1
end
if (@item > '') begin
insert into @t values (@item)
end
return
end
像这样测试它:
select * from SplitWordList('Hello John Smith')
这里有一个UDF可以做到这一点。它将返回一个带分隔符的值的表，我还没有尝试所有的场景，但您的示例工作良好。
CREATE FUNCTION SplitString
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)
--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0
Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))
Insert Into @ReturnTable(part)
Select @part
Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))
Select @iSpaces = charindex(@deliminator,@myString,0)
end
If len(@myString) > 0
Insert Into @ReturnTable
Select @myString
RETURN
END
GO
你可以这样称呼它:
Select * From SplitString('Hello John Smith',' ')
编辑:使用len>1处理分隔符的更新解决方案如下:
select * From SplitString('Hello**John**Smith','**')
一个简单的优化算法:
ALTER FUNCTION [dbo].[Split]( @Text NVARCHAR(200),@Splitor CHAR(1) )
RETURNS @Result TABLE ( value NVARCHAR(50))
AS
BEGIN
DECLARE @PathInd INT
Set @Text+=@Splitor
WHILE LEN(@Text) > 0
BEGIN
SET @PathInd=PATINDEX('%'+@Splitor+'%',@Text)
INSERT INTO @Result VALUES(SUBSTRING(@Text, 0, @PathInd))
SET @Text= SUBSTRING(@Text, @PathInd+1, LEN(@Text))
END
RETURN
END
我一直在使用vzczc的答案使用递归cte的一段时间，但一直想更新它来处理可变长度分隔符，也处理字符串与前驱和滞后“分隔符”，如当你有一个csv文件的记录，如:
“鲍勃”,“史密斯”桑尼维尔”,“CA”
或者当你处理如下所示的六部分fqn时。我广泛地使用这些来记录subject_fqn的审计，错误处理等，parsename只处理四个部分:
[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]
这是我的更新版本，感谢vzczc的原始帖子!
select * from [utility].[split_string](N'"this"."string"."gets"."split"."and"."removes"."leading"."and"."trailing"."quotes"', N'"."', N'"', N'"');
select * from [utility].[split_string](N'"this"."string"."gets"."split"."but"."leaves"."leading"."and"."trailing"."quotes"', N'"."', null, null);
select * from [utility].[split_string](N'[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]', N'].[', N'[', N']');
create function [utility].[split_string] (
@input [nvarchar](max)
, @separator [sysname]
, @lead [sysname]
, @lag [sysname])
returns @node_list table (
[index] [int]
, [node] [nvarchar](max))
begin
declare @separator_length [int]= len(@separator)
, @lead_length [int] = isnull(len(@lead), 0)
, @lag_length [int] = isnull(len(@lag), 0);
--
set @input = right(@input, len(@input) - @lead_length);
set @input = left(@input, len(@input) - @lag_length);
--
with [splitter]([index], [starting_position], [start_location])
as (select cast(@separator_length as [bigint])
, cast(1 as [bigint])
, charindex(@separator, @input)
union all
select [index] + 1
, [start_location] + @separator_length
, charindex(@separator, @input, [start_location] + @separator_length)
from [splitter]
where [start_location] > 0)
--
insert into @node_list
([index],[node])
select [index] - @separator_length as [index]
, substring(@input, [starting_position], case
when [start_location] > 0
then
[start_location] - [starting_position]
else
len(@input)
end) as [node]
from [splitter];
--
return;
end;
go
这里的大多数解决方案使用while循环或递归cte。我保证，如果你可以使用空格以外的分隔符，基于集合的方法会更好:
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM
(
SELECT n = Number,
[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)) = @Delim
) AS y
);
示例用法:
SELECT Value FROM dbo.SplitString('foo,bar,blat,foo,splunge',',')
WHERE idx = 3;
结果:
----
blat
您还可以将需要的idx作为参数添加到函数中，但我将把它作为练习留给读者。
您不能仅使用SQL Server 2016中添加的本地STRING_SPLIT函数来实现这一点，因为不能保证输出将按照原始列表的顺序呈现。换句话说，如果你传递3,6,1结果可能是这个顺序，但它可能是1,3,6。我已经在这里请求社区的帮助来改进内置功能:
请帮助改进STRING_SPLIT
有了足够的定性反馈，他们可能会考虑做出以下改进:
STRING_SPLIT不是特性完整的
更多关于拆分函数，为什么(和证明)while循环和递归cte不能扩展，以及更好的替代方案，如果拆分字符串来自应用层:
以正确的方式拆分字符串-或者退而求其次的方式 拆分字符串:后续 分割字符串:现在使用更少的T-SQL 比较字符串分割/连接方法 处理一个整数列表:我的方法 拆分整数列表:另一个汇总 更多关于拆分列表的内容:自定义分隔符、防止重复和维护顺序 在SQL Server中删除字符串中的重复项
在SQL Server 2016或更高版本上，你应该看看STRING_SPLIT()和STRING_AGG():
性能惊喜和假设:STRING_SPLIT() STRING_SPLIT()在SQL Server 2016:后续#1 STRING_SPLIT()在SQL Server 2016:后续#2 SQL Server v.Next: STRING_AGG()性能 使用SQL Server的新STRING_AGG和STRING_SPLIT函数解决老问题
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
