使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
这个问题不是关于字符串分割方法,而是关于如何获取第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
其他回答
可以利用Number表进行字符串解析。
创建一个物理数字表:
create table dbo.Numbers (N int primary key);
insert into dbo.Numbers
select top 1000 row_number() over(order by number) from master..spt_values
go
创建具有1000000行的测试表
create table #yak (i int identity(1,1) primary key, array varchar(50))
insert into #yak(array)
select 'a,b,c' from dbo.Numbers n cross join dbo.Numbers nn
go
创建函数
create function [dbo].[ufn_ParseArray]
( @Input nvarchar(4000),
@Delimiter char(1) = ',',
@BaseIdent int
)
returns table as
return
( select row_number() over (order by n asc) + (@BaseIdent - 1) [i],
substring(@Input, n, charindex(@Delimiter, @Input + @Delimiter, n) - n) s
from dbo.Numbers
where n <= convert(int, len(@Input)) and
substring(@Delimiter + @Input, n, 1) = @Delimiter
)
go
使用情况(在我的笔记本电脑上40秒内输出3mil行)
select *
from #yak
cross apply dbo.ufn_ParseArray(array, ',', 1)
清理
drop table dbo.Numbers;
drop function [dbo].[ufn_ParseArray]
这里的性能并不惊人,但在100万行表上调用函数并不是最好的主意。如果将字符串拆分到多行,我会避免使用该函数。
递归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 |
使用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个元素。
CREATE TABLE test(
id int,
adress varchar(100)
);
INSERT INTO test VALUES(1, 'Ludovic Aubert, 42 rue de la Victoire, 75009, Paris, France'),(2, 'Jose Garcia, 1 Calle de la Victoria, 56500 Barcelona, Espana');
SELECT id, value, COUNT(*) OVER (PARTITION BY id) AS n, ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS rn, adress
FROM test
CROSS APPLY STRING_SPLIT(adress, ',')
这个模式工作得很好,可以进行推广
Convert(xml,'<n>'+Replace(FIELD,'.','</n><n>')+'</n>').value('(/n[INDEX])','TYPE')
^^^^^ ^^^^^ ^^^^
注意字段,索引和类型。
让一些表具有类似的标识符
sys.message.1234.warning.A45
sys.message.1235.error.O98
....
然后,你就可以写作了
SELECT Source = q.value('(/n[1])', 'varchar(10)'),
RecordType = q.value('(/n[2])', 'varchar(20)'),
RecordNumber = q.value('(/n[3])', 'int'),
Status = q.value('(/n[4])', 'varchar(5)')
FROM (
SELECT q = Convert(xml,'<n>'+Replace(fieldName,'.','</n><n>')+'</n>')
FROM some_TABLE
) Q
拆铸所有零件。