使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
我一直在使用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
其他回答
以下是我的解决方案,可能会对某些人有所帮助。修改以上Jonesinator的回答。
如果我有一个带分隔符的INT值字符串,并希望返回一个INT表(然后我可以加入)。如。44岁的1,3343 6,8765年
创建一个UDF:
IF OBJECT_ID(N'dbo.ufn_GetIntTableFromDelimitedList', N'TF') IS NOT NULL
DROP FUNCTION dbo.[ufn_GetIntTableFromDelimitedList];
GO
CREATE FUNCTION dbo.[ufn_GetIntTableFromDelimitedList](@String NVARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @table TABLE
(
Value INT NOT NULL
)
AS
BEGIN
DECLARE @Pattern NVARCHAR(3)
SET @Pattern = '%' + @Delimiter + '%'
DECLARE @Value NVARCHAR(MAX)
WHILE LEN(@String) > 0
BEGIN
IF PATINDEX(@Pattern, @String) > 0
BEGIN
SET @Value = SUBSTRING(@String, 0, PATINDEX(@Pattern, @String))
INSERT INTO @table (Value) VALUES (@Value)
SET @String = SUBSTRING(@String, LEN(@Value + @Delimiter) + 1, LEN(@String))
END
ELSE
BEGIN
-- Just the one value.
INSERT INTO @table (Value) VALUES (@String)
RETURN
END
END
RETURN
END
GO
然后得到表格结果:
SELECT * FROM dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',')
1
20
3
343
44
6
8765
在join语句中:
SELECT [ID], [FirstName]
FROM [User] u
JOIN dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',') t ON u.[ID] = t.[Value]
1 Elvis
20 Karen
3 David
343 Simon
44 Raj
6 Mike
8765 Richard
如果你想返回一个nvarchar列表而不是int,那么只需更改表定义:
RETURNS @table TABLE
(
Value NVARCHAR(MAX) NOT NULL
)
我知道这是一个老问题,但我认为有人可以从我的解决方案中受益。
select
SUBSTRING(column_name,1,CHARINDEX(' ',column_name,1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
,1
,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)+1
,LEN(column_name))
from table_name
SQL小提琴
优点:
它用' '分隔所有3个子字符串。 不能使用while循环,因为它会降低性能。 不需要枢轴,因为所有的结果子字符串将显示在 一行
限制:
一个人必须知道绝对的不。Of Spaces(子字符串)。
注:解决方案可以给出最多N个子字符串。
为了克服这个限制,我们可以使用下面的参考。
但是上面的解决方案不能在表中使用(实际上我不能使用它)。
我希望这个解决方案能帮助到一些人。
更新:在记录> 50000的情况下,不建议使用LOOPS,因为它会降低性能
这里有一个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','**')
我开发了这个,
declare @x nvarchar(Max) = 'ali.veli.deli.';
declare @item nvarchar(Max);
declare @splitter char='.';
while CHARINDEX(@splitter,@x) != 0
begin
set @item = LEFT(@x,CHARINDEX(@splitter,@x))
set @x = RIGHT(@x,len(@x)-len(@item) )
select @item as item, @x as x;
end
你唯一应该注意的是。'。那@x的末尾就应该在这里。
使用STRING_SPLIT的现代方法需要SQL Server 2016及以上版本。
DECLARE @string varchar(100) = 'Hello John Smith'
SELECT
ROW_NUMBER() OVER (ORDER BY value) AS RowNr,
value
FROM string_split(@string, ' ')
结果:
RowNr value
1 Hello
2 John
3 Smith
现在可以从行号中得到第n个元素。