使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
从SQL Server 2016开始,我们使用string_split
DECLARE @string varchar(100) = 'Richard, Mike, Mark'
SELECT value FROM string_split(@string, ',')
其他回答
通过delimeter函数得到字符串的n个部分:
create function GetStringPartByDelimeter (
@value as nvarchar(max),
@delimeter as nvarchar(max),
@position as int
) returns NVARCHAR(MAX)
AS BEGIN
declare @startPos as int
declare @endPos as int
set @endPos = -1
while (@position > 0 and @endPos != 0) begin
set @startPos = @endPos + 1
set @endPos = charindex(@delimeter, @value, @startPos)
if(@position = 1) begin
if(@endPos = 0)
set @endPos = len(@value) + 1
return substring(@value, @startPos, @endPos - @startPos)
end
set @position = @position - 1
end
return null
end
以及用法:
select dbo.GetStringPartByDelimeter ('a;b;c;d;e', ';', 3)
返回:
c
我一直在使用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
好吧,我的代码并不那么简单,但下面是我用来将逗号分隔的输入变量分割为单个值,并将其放入表变量中的代码。我相信您可以稍微修改一下,根据空格进行分割,然后对该表变量执行基本的SELECT查询以获得结果。
-- Create temporary table to parse the list of accounting cycles.
DECLARE @tblAccountingCycles table
(
AccountingCycle varchar(10)
)
DECLARE @vchAccountingCycle varchar(10)
DECLARE @intPosition int
SET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + ','
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)
IF REPLACE(@vchAccountingCycleIDs, ',', '') <> ''
BEGIN
WHILE @intPosition > 0
BEGIN
SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1)))
IF @vchAccountingCycle <> ''
BEGIN
INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle)
END
SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition)
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)
END
END
概念是差不多的。另一种选择是利用SQL Server 2005本身的. net兼容性。实际上,您可以在. net中编写一个简单的方法,将字符串分割,然后将其作为存储过程/函数公开。
首先,创建一个函数(使用CTE,公共表表达式不再需要临时表)
create function dbo.SplitString
(
@str nvarchar(4000),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
1,
1,
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 zeroBasedOccurance,
substring(
@str,
a,
case when b > 0 then b-a ELSE 4000 end)
AS s
from tokens
)
GO
然后,像这样使用它作为任何表(或修改它以适应现有存储的proc)。
select s
from dbo.SplitString('Hello John Smith', ' ')
where zeroBasedOccurance=1
更新
以前的版本将失败的输入字符串长度超过4000个字符。这个版本考虑到了以下限制:
create function dbo.SplitString
(
@str nvarchar(max),
@separator char(1)
)
returns table
AS
return (
with tokens(p, a, b) AS (
select
cast(1 as bigint),
cast(1 as bigint),
charindex(@separator, @str)
union all
select
p + 1,
b + 1,
charindex(@separator, @str, b + 1)
from tokens
where b > 0
)
select
p-1 ItemIndex,
substring(
@str,
a,
case when b > 0 then b-a ELSE LEN(@str) end)
AS s
from tokens
);
GO
用法不变。
你可以在SQL用户定义函数解析带分隔符的字符串中找到有用的解决方案(来自代码项目)。
你可以使用这个简单的逻辑:
Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null
WHILE LEN(@products) > 0
BEGIN
IF PATINDEX('%|%', @products) > 0
BEGIN
SET @individual = SUBSTRING(@products,
0,
PATINDEX('%|%', @products))
SELECT @individual
SET @products = SUBSTRING(@products,
LEN(@individual + '|') + 1,
LEN(@products))
END
ELSE
BEGIN
SET @individual = @products
SET @products = NULL
SELECT @individual
END
END