使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
在Azure SQL数据库(基于Microsoft SQL Server但不完全相同的东西)中,STRING_SPLIT函数的签名看起来像这样:
STRING_SPLIT ( string , separator [ , enable_ordinal ] )
当enable_ordinal标志设置为1时,结果将包括一个名为ordinal的列,该列由输入字符串中子字符串的基于1的位置组成:
SELECT *
FROM STRING_SPLIT('hello john smith', ' ', 1)
| value | ordinal |
|-------|---------|
| hello | 1 |
| john | 2 |
| smith | 3 |
这允许我们这样做:
SELECT value
FROM STRING_SPLIT('hello john smith', ' ', 1)
WHERE ordinal = 2
| value |
|-------|
| john |
如果enable_ordinal不可用,则有一个技巧,即假定输入字符串中的子字符串是惟一的。在这种情况下,CHAR_INDEX可以用来查找子字符串在输入字符串中的位置:
SELECT value, ROW_NUMBER() OVER (ORDER BY CHARINDEX(value, input_str)) AS ord_pos
FROM (VALUES
('hello john smith')
) AS x(input_str)
CROSS APPLY STRING_SPLIT(input_str, ' ')
| value | ord_pos |
|-------+---------|
| hello | 1 |
| john | 2 |
| smith | 3 |
其他回答
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)
一个简单的优化算法:
ALTER FUNCTION [dbo].[Split]( @Text NVARCHAR(200),@Splitor CHAR(1) )
RETURNS @Result TABLE ( value NVARCHAR(50))
AS
BEGIN
DECLARE @PathInd INT
Set @Text+=@Splitor
WHILE LEN(@Text) > 0
BEGIN
SET @PathInd=PATINDEX('%'+@Splitor+'%',@Text)
INSERT INTO @Result VALUES(SUBSTRING(@Text, 0, @PathInd))
SET @Text= SUBSTRING(@Text, @PathInd+1, LEN(@Text))
END
RETURN
END
这个模式工作得很好,可以进行推广
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
拆铸所有零件。
你可以在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
可以利用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万行表上调用函数并不是最好的主意。如果将字符串拆分到多行,我会避免使用该函数。