使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
建立在@NothingsImpossible解决方案上,或者,更确切地说,评论投票最多的答案(就在接受的答案下面),我发现下面的快速和肮脏的解决方案满足了我自己的需求-它的好处是完全在SQL域内。
给定一个字符串"first;second;third;fourth;fifth",比如说,我想获取第三个标记。只有当我们知道字符串将有多少标记时,这才有效——在这种情况下,它是5。所以我的操作方式是将最后两个令牌切掉(内部查询),然后将前两个令牌切掉(外部查询)
我知道这是丑陋的,涵盖了我所处的具体情况,但我张贴它只是为了以防有人发现它有用。干杯
select
REVERSE(
SUBSTRING(
reverse_substring,
0,
CHARINDEX(';', reverse_substring)
)
)
from
(
select
msg,
SUBSTRING(
REVERSE(msg),
CHARINDEX(
';',
REVERSE(msg),
CHARINDEX(
';',
REVERSE(msg)
)+1
)+1,
1000
) reverse_substring
from
(
select 'first;second;third;fourth;fifth' msg
) a
) b
其他回答
首先,创建一个函数(使用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
用法不变。
试试这个:
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')
这个模式工作得很好,可以进行推广
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
拆铸所有零件。
一个简单的优化算法:
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
在这里我发布了一个简单的解决方法
CREATE FUNCTION [dbo].[split](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
像这样执行函数
select * from dbo.split('Hello John Smith',' ')