使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
下面的示例使用递归CTE
更新18.09.2013
CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))
RETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
AS
BEGIN
;WITH cte AS
(
SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter, @List + @Delimiter)) AS val,
CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), '') AS nvarchar(max)) AS stval,
1 AS [level]
UNION ALL
SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)),
[level] + 1
FROM cte
WHERE stval != ''
)
INSERT @returns
SELECT REPLACE(val, ' ','' ) AS val, [level]
FROM cte
WHERE val > ''
RETURN
END
演示SQLFiddle
其他回答
试试这个:
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')
首先,创建一个函数(使用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
用法不变。
使用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个元素。
递归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 |
使用字符串和values()语句怎么样?
DECLARE @str varchar(max)
SET @str = 'Hello John Smith'
DECLARE @separator varchar(max)
SET @separator = ' '
DECLARE @Splited TABLE(id int IDENTITY(1,1), item varchar(max))
SET @str = REPLACE(@str, @separator, '''),(''')
SET @str = 'SELECT * FROM (VALUES(''' + @str + ''')) AS V(A)'
INSERT INTO @Splited
EXEC(@str)
SELECT * FROM @Splited
结果集。
id item
1 Hello
2 John
3 Smith