使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
以下是我的解决方案,可能会对某些人有所帮助。修改以上Jonesinator的回答。
如果我有一个带分隔符的INT值字符串,并希望返回一个INT表(然后我可以加入)。如。44岁的1,3343 6,8765年
创建一个UDF:
IF OBJECT_ID(N'dbo.ufn_GetIntTableFromDelimitedList', N'TF') IS NOT NULL
DROP FUNCTION dbo.[ufn_GetIntTableFromDelimitedList];
GO
CREATE FUNCTION dbo.[ufn_GetIntTableFromDelimitedList](@String NVARCHAR(MAX), @Delimiter CHAR(1))
RETURNS @table TABLE
(
Value INT NOT NULL
)
AS
BEGIN
DECLARE @Pattern NVARCHAR(3)
SET @Pattern = '%' + @Delimiter + '%'
DECLARE @Value NVARCHAR(MAX)
WHILE LEN(@String) > 0
BEGIN
IF PATINDEX(@Pattern, @String) > 0
BEGIN
SET @Value = SUBSTRING(@String, 0, PATINDEX(@Pattern, @String))
INSERT INTO @table (Value) VALUES (@Value)
SET @String = SUBSTRING(@String, LEN(@Value + @Delimiter) + 1, LEN(@String))
END
ELSE
BEGIN
-- Just the one value.
INSERT INTO @table (Value) VALUES (@String)
RETURN
END
END
RETURN
END
GO
然后得到表格结果:
SELECT * FROM dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',')
1
20
3
343
44
6
8765
在join语句中:
SELECT [ID], [FirstName]
FROM [User] u
JOIN dbo.[ufn_GetIntTableFromDelimitedList]('1,20,3,343,44,6,8765', ',') t ON u.[ID] = t.[Value]
1 Elvis
20 Karen
3 David
343 Simon
44 Raj
6 Mike
8765 Richard
如果你想返回一个nvarchar列表而不是int,那么只需更改表定义:
RETURNS @table TABLE
(
Value NVARCHAR(MAX) NOT NULL
)
其他回答
我使用弗雷德里克的答案,但这在SQL Server 2005中不起作用
我修改了它,我使用select with union all,它可以工作
DECLARE @str varchar(max)
SET @str = 'Hello John Smith how are you'
DECLARE @separator varchar(max)
SET @separator = ' '
DECLARE @Splited table(id int IDENTITY(1,1), item varchar(max))
SET @str = REPLACE(@str, @separator, ''' UNION ALL SELECT ''')
SET @str = ' SELECT ''' + @str + ''' '
INSERT INTO @Splited
EXEC(@str)
SELECT * FROM @Splited
结果集是:
id item
1 Hello
2 John
3 Smith
4 how
5 are
6 you
我开发了这个,
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的末尾就应该在这里。
基于纯集的解决方案,使用TVF和递归CTE。您可以将此函数JOIN和APPLY到任何数据集。
create function [dbo].[SplitStringToResultSet] (@value varchar(max), @separator char(1))
returns table
as return
with r as (
select value, cast(null as varchar(max)) [x], -1 [no] from (select rtrim(cast(@value as varchar(max))) [value]) as j
union all
select right(value, len(value)-case charindex(@separator, value) when 0 then len(value) else charindex(@separator, value) end) [value]
, left(r.[value], case charindex(@separator, r.value) when 0 then len(r.value) else abs(charindex(@separator, r.[value])-1) end ) [x]
, [no] + 1 [no]
from r where value > '')
select ltrim(x) [value], [no] [index] from r where x is not null;
go
用法:
select *
from [dbo].[SplitStringToResultSet]('Hello John Smith', ' ')
where [index] = 1;
结果:
value index
-------------
John 1
使用字符串和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
使用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个元素。