使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
可以利用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万行表上调用函数并不是最好的主意。如果将字符串拆分到多行,我会避免使用该函数。
其他回答
Alter Function dbo.fn_Split
(
@Expression nvarchar(max),
@Delimiter nvarchar(20) = ',',
@Qualifier char(1) = Null
)
RETURNS @Results TABLE (id int IDENTITY(1,1), value nvarchar(max))
AS
BEGIN
/* USAGE
Select * From dbo.fn_Split('apple pear grape banana orange honeydew cantalope 3 2 1 4', ' ', Null)
Select * From dbo.fn_Split('1,abc,"Doe, John",4', ',', '"')
Select * From dbo.fn_Split('Hello 0,"&""&&&&', ',', '"')
*/
-- Declare Variables
DECLARE
@X xml,
@Temp nvarchar(max),
@Temp2 nvarchar(max),
@Start int,
@End int
-- HTML Encode @Expression
Select @Expression = (Select @Expression For XML Path(''))
-- Find all occurences of @Delimiter within @Qualifier and replace with |||***|||
While PATINDEX('%' + @Qualifier + '%', @Expression) > 0 AND Len(IsNull(@Qualifier, '')) > 0
BEGIN
Select
-- Starting character position of @Qualifier
@Start = PATINDEX('%' + @Qualifier + '%', @Expression),
-- @Expression starting at the @Start position
@Temp = SubString(@Expression, @Start + 1, LEN(@Expression)-@Start+1),
-- Next position of @Qualifier within @Expression
@End = PATINDEX('%' + @Qualifier + '%', @Temp) - 1,
-- The part of Expression found between the @Qualifiers
@Temp2 = Case When @End < 0 Then @Temp Else Left(@Temp, @End) End,
-- New @Expression
@Expression = REPLACE(@Expression,
@Qualifier + @Temp2 + Case When @End < 0 Then '' Else @Qualifier End,
Replace(@Temp2, @Delimiter, '|||***|||')
)
END
-- Replace all occurences of @Delimiter within @Expression with '</fn_Split><fn_Split>'
-- And convert it to XML so we can select from it
SET
@X = Cast('<fn_Split>' +
Replace(@Expression, @Delimiter, '</fn_Split><fn_Split>') +
'</fn_Split>' as xml)
-- Insert into our returnable table replacing '|||***|||' back to @Delimiter
INSERT @Results
SELECT
"Value" = LTRIM(RTrim(Replace(C.value('.', 'nvarchar(max)'), '|||***|||', @Delimiter)))
FROM
@X.nodes('fn_Split') as X(C)
-- Return our temp table
RETURN
END
首先,创建一个函数(使用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
用法不变。
我使用弗雷德里克的答案,但这在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
下面的示例使用递归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
以下是我的解决方案,可能会对某些人有所帮助。修改以上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
)