使用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
其他回答
这里有一个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','**')
I realize this is a really old question, but starting with SQL Server 2016 there are functions for parsing JSON data that can be used to specifically address the OP's question--and without splitting strings or resorting to a user-defined function. To access an item at a particular index of a delimited string, use the JSON_VALUE function. Properly formatted JSON data is required, however: strings must be enclosed in double quotes " and the delimiter must be a comma ,, with the entire string enclosed in square brackets [].
DECLARE @SampleString NVARCHAR(MAX) = '"Hello John Smith"';
--Format as JSON data.
SET @SampleString = '[' + REPLACE(@SampleString, ' ', '","') + ']';
SELECT
JSON_VALUE(@SampleString, '$[0]') AS Element1Value,
JSON_VALUE(@SampleString, '$[1]') AS Element2Value,
JSON_VALUE(@SampleString, '$[2]') AS Element3Value;
输出
Element1Value Element2Value Element3Value
--------------------- ------------------- ------------------------------
Hello John Smith
(1 row affected)
建立在@NothingsImpossible解决方案上,或者,更确切地说,评论投票最多的答案(就在接受的答案下面),我发现下面的快速和肮脏的解决方案满足了我自己的需求-它的好处是完全在SQL域内。
给定一个字符串"first;second;third;fourth;fifth",比如说,我想获取第三个标记。只有当我们知道字符串将有多少标记时,这才有效——在这种情况下,它是5。所以我的操作方式是将最后两个令牌切掉(内部查询),然后将前两个令牌切掉(外部查询)
我知道这是丑陋的,涵盖了我所处的具体情况,但我张贴它只是为了以防有人发现它有用。干杯
select
REVERSE(
SUBSTRING(
reverse_substring,
0,
CHARINDEX(';', reverse_substring)
)
)
from
(
select
msg,
SUBSTRING(
REVERSE(msg),
CHARINDEX(
';',
REVERSE(msg),
CHARINDEX(
';',
REVERSE(msg)
)+1
)+1,
1000
) reverse_substring
from
(
select 'first;second;third;fourth;fifth' msg
) a
) b
修改@Aaron Bertrand的功能
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255),
@Idx int
)
RETURNS NVARCHAR(1000)
AS
BEGIN
DECLARE @ValueTable TABLE(String NVARCHAR(50), Ind int)
DECLARE @Value NVARCHAR(50)
BEGIN
INSERT INTO @ValueTable
SELECT Value, idx FROM
(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
) AS R WHERE idx = @Idx
SET @Value = (SELECT String FROM @ValueTable)
END
RETURN @Value
END
GO
我在网上寻找解决方案,下面的工作对我来说。 Ref。
然后像这样调用函数:
SELECT * FROM dbo.split('ram shyam hari gopal',' ')
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @temptable TABLE (items VARCHAR(8000))
AS
BEGIN
DECLARE @idx INT
DECLARE @slice VARCHAR(8000)
SELECT @idx = 1
IF len(@String)<1 OR @String IS NULL RETURN
WHILE @idx!= 0
BEGIN
SET @idx = charindex(@Delimiter,@String)
IF @idx!=0
SET @slice = LEFT(@String,@idx - 1)
ELSE
SET @slice = @String
IF(len(@slice)>0)
INSERT INTO @temptable(Items) VALUES(@slice)
SET @String = RIGHT(@String,len(@String) - @idx)
IF len(@String) = 0 break
END
RETURN
END