使用SQL Server，我如何分割一个字符串，以便我可以访问项目x?
拿一根“你好，约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
好吧，我的代码并不那么简单，但下面是我用来将逗号分隔的输入变量分割为单个值，并将其放入表变量中的代码。我相信您可以稍微修改一下，根据空格进行分割，然后对该表变量执行基本的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中编写一个简单的方法，将字符串分割，然后将其作为存储过程/函数公开。
其他回答
好吧，我的代码并不那么简单，但下面是我用来将逗号分隔的输入变量分割为单个值，并将其放入表变量中的代码。我相信您可以稍微修改一下，根据空格进行分割，然后对该表变量执行基本的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解决方案与服务器疼痛，测试它
MS SQL Server 2008模式设置:
create table Course( Courses varchar(100) );
insert into Course values ('Hello John Smith');
查询1:
with cte as
( select
left( Courses, charindex( ' ' , Courses) ) as a_l,
cast( substring( Courses,
charindex( ' ' , Courses) + 1 ,
len(Courses ) ) + ' '
as varchar(100) ) as a_r,
Courses as a,
0 as n
from Course t
union all
select
left(a_r, charindex( ' ' , a_r) ) as a_l,
substring( a_r, charindex( ' ' , a_r) + 1 , len(a_R ) ) as a_r,
cte.a,
cte.n + 1 as n
from Course t inner join cte
on t.Courses = cte.a and len( a_r ) > 0
)
select a_l, n from cte
--where N = 1
结果:
| A_L | N |
|--------|---|
| Hello | 0 |
| John | 1 |
| Smith | 2 |
这个问题不是关于字符串分割方法，而是关于如何获取第n个元素。
这里所有的答案都是使用递归，CTEs，多个CHARINDEX, REVERSE和PATINDEX，发明函数，调用CLR方法，数字表，CROSS APPLYs…大多数答案都包含了很多行代码。
但是-如果你真的只想要一个方法来获取第n个元素-这可以作为真正的一行程序来完成，没有UDF，甚至没有子选择…作为一个额外的好处:类型安全
用空格分隔第2部分:
DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')
当然，你可以使用变量作为分隔符和位置(使用sql:column直接从查询值中检索位置):
DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')
如果您的字符串可能包含禁止字符(特别是&><中的一个字符)，您仍然可以这样做。只需首先在字符串上使用FOR XML PATH，隐式地用合适的转义序列替换所有禁止的字符。
另外，如果分隔符是分号，这是一种非常特殊的情况。在这种情况下，我首先将分隔符替换为“#DLMT#”，最后将其替换为XML标记:
SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');
更新SQL-Server 2016+
遗憾的是，开发人员忘记使用STRING_SPLIT返回部件的索引。但是，使用SQL-Server 2016+，有JSON_VALUE和OPENJSON。
使用JSON_VALUE，我们可以传入位置作为索引数组。
对于OPENJSON，文档清楚地说明:
当OPENJSON解析JSON数组时，该函数将JSON文本中元素的索引作为键返回。
像1,2,3这样的字符串只需要括号:[1,2,3]。 像这样的一串单词需要是["this"，"is"，"an"，"example"]。 这些都是非常简单的字符串操作。试试吧:
DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;
--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));
-位置安全的字符串分配器(从零开始):
SELECT JsonArray.[key] AS [Position]
,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray
在这篇文章中，我测试了各种方法，发现OPENJSON真的很快。甚至比著名的“delimitedSplit8k()”方法快得多……
更新2 -获取类型安全的值
只需使用double[[]]，就可以在数组中使用数组。这允许使用类型化的with子句:
DECLARE @SomeDelimitedString VARCHAR(100)='part1|1|20190920';
DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');
SELECT @SomeDelimitedString AS TheOriginal
,@JsonArray AS TransformedToJSON
,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment VARCHAR(100) '$[0]'
,TheSecondFragment INT '$[1]'
,TheThirdFragment DATE '$[2]') ValuesFromTheArray
试试这个:
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')
Aaron Bertrand的回答很好，但也有缺陷。它不能准确地将空格作为分隔符处理(就像最初问题中的示例一样)，因为长度函数将空格带在后面。
下面是他的代码，稍微调整了一下，允许使用空格分隔符:
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT
[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+'x')-1) = @Delim
) AS y
);
推荐文章
- 如何在Ruby On Rails中使用NuoDB手动执行SQL命令
- 查询JSON类型内的数组元素
- 确定记录是否存在的最快方法
- 获得PostgreSQL数据库中当前连接数的正确查询
- 在SQL选择语句Order By 1的目的是什么?
- 从现有模式生成表关系图(SQL Server)
- 我如何循环通过一组记录在SQL Server?
- 数据库和模式的区别
- 如何在SQL Server中一次更改多个列
- 如何从命令行通过mysql运行一个查询?
- 外键约束可能导致循环或多条级联路径?
- 使用LIMIT/OFFSET运行查询，还可以获得总行数
- 当恢复sql时，psql无效命令\N
- 货币应该使用哪种数据类型?
- 如何选择每一行的列值不是独特的
