使用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 |
其他回答
我开发了这个,
declare @x nvarchar(Max) = 'ali.veli.deli.';
declare @item nvarchar(Max);
declare @splitter char='.';
while CHARINDEX(@splitter,@x) != 0
begin
set @item = LEFT(@x,CHARINDEX(@splitter,@x))
set @x = RIGHT(@x,len(@x)-len(@item) )
select @item as item, @x as x;
end
你唯一应该注意的是。'。那@x的末尾就应该在这里。
这个模式工作得很好,可以进行推广
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
拆铸所有零件。
可以利用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万行表上调用函数并不是最好的主意。如果将字符串拆分到多行,我会避免使用该函数。
下面是一个函数,它将完成问题的目标,即分割字符串并访问项目X:
CREATE FUNCTION [dbo].[SplitString]
(
@List VARCHAR(MAX),
@Delimiter VARCHAR(255),
@ElementNumber INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @inp VARCHAR(MAX)
SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH(''))
DECLARE @xml XML
SET @xml = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'
DECLARE @ret VARCHAR(MAX)
SET @ret = (SELECT
el = split.el.value('.','varchar(max)')
FROM @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el))
RETURN @ret
END
用法:
SELECT dbo.SplitString('Hello John Smith', ' ', 2)
结果:
John
好吧,我的代码并不那么简单,但下面是我用来将逗号分隔的输入变量分割为单个值,并将其放入表变量中的代码。我相信您可以稍微修改一下,根据空格进行分割,然后对该表变量执行基本的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中编写一个简单的方法,将字符串分割,然后将其作为存储过程/函数公开。