使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
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, ',')
其他回答
我不相信SQL Server有内置的分裂函数,所以除了UDF,我知道的唯一其他答案是劫持PARSENAME函数:
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)
PARSENAME接受一个字符串,并根据句点分隔它。它接受一个数字作为第二个参数,该数字指定要返回字符串的哪一部分(从后到前工作)。
SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3) --return Hello
明显的问题是当字符串已经包含句点时。我仍然认为使用UDF是最好的方式……还有其他建议吗?
试试这个:
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')
建立在@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
在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 |
首先,创建一个函数(使用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
用法不变。