使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
首先,创建一个函数(使用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
用法不变。
其他回答
基于纯集的解决方案,使用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
下面是一个SQL UDF,它可以分割字符串并只抓取特定的部分。
create FUNCTION [dbo].[udf_SplitParseOut]
(
@List nvarchar(MAX),
@SplitOn nvarchar(5),
@GetIndex smallint
)
returns varchar(1000)
AS
BEGIN
DECLARE @RtnValue table
(
Id int identity(0,1),
Value nvarchar(MAX)
)
DECLARE @result varchar(1000)
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
select @result = value from @RtnValue where ID = @GetIndex
Return @result
END
虽然类似于josejuan基于XML的回答,但我发现只处理一次XML路径,然后旋转稍微更有效:
select ID,
[3] as PathProvidingID,
[4] as PathProvider,
[5] as ComponentProvidingID,
[6] as ComponentProviding,
[7] as InputRecievingID,
[8] as InputRecieving,
[9] as RowsPassed,
[10] as InputRecieving2
from
(
select id,message,d.* from sysssislog cross apply (
SELECT Item = y.i.value('(./text())[1]', 'varchar(200)'),
row_number() over(order by y.i) as rn
FROM
(
SELECT x = CONVERT(XML, '<i>' + REPLACE(Message, ':', '</i><i>') + '</i>').query('.')
) AS a CROSS APPLY x.nodes('i') AS y(i)
) d
WHERE event
=
'OnPipelineRowsSent'
) as tokens
pivot
( max(item) for [rn] in ([3],[4],[5],[6],[7],[8],[9],[10])
) as data
8:30开始
select id,
tokens.value('(/n[3])', 'varchar(100)')as PathProvidingID,
tokens.value('(/n[4])', 'varchar(100)') as PathProvider,
tokens.value('(/n[5])', 'varchar(100)') as ComponentProvidingID,
tokens.value('(/n[6])', 'varchar(100)') as ComponentProviding,
tokens.value('(/n[7])', 'varchar(100)') as InputRecievingID,
tokens.value('(/n[8])', 'varchar(100)') as InputRecieving,
tokens.value('(/n[9])', 'varchar(100)') as RowsPassed
from
(
select id, Convert(xml,'<n>'+Replace(message,'.','</n><n>')+'</n>') tokens
from sysssislog
WHERE event
=
'OnPipelineRowsSent'
) as data
9点20分跑
几乎所有其他答案都是替换正在分割的字符串,这浪费了CPU周期并执行不必要的内存分配。
我在这里介绍了一种更好的进行字符串拆分的方法:http://www.digitalruby.com/split-string-sql-server/
代码如下:
SET NOCOUNT ON
-- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = 'your|string|to|split|here'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = '|'
DECLARE @SplitStartPos int = 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
WHILE @SplitEndPos > 0
BEGIN
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
INSERT @SplitStringTable (Value) VALUES (@SplitValue)
SET @SplitStartPos = @SplitEndPos + 1
SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END
SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)
SET NOCOUNT OFF
-- You can select or join with the values in @SplitStringTable at this point.
如果数据库的兼容性级别为130或更高,则可以使用STRING_SPLIT函数和OFFSET FETCH子句按索引获取特定的项。
要获得索引N(从零开始)的项,可以使用以下代码
SELECT value
FROM STRING_SPLIT('Hello John Smith',' ')
ORDER BY (SELECT NULL)
OFFSET N ROWS
FETCH NEXT 1 ROWS ONLY
要检查数据库的兼容性级别,执行以下代码:
SELECT compatibility_level
FROM sys.databases WHERE name = 'YourDBName';