使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
使用SQL Server,我如何分割一个字符串,以便我可以访问项目x?
拿一根“你好,约翰·史密斯”的绳子。我如何通过空格分割字符串并访问索引1应该返回“约翰”的项目?
当前回答
基于纯集的解决方案,使用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
其他回答
Aaron Bertrand的回答很好,但也有缺陷。它不能准确地将空格作为分隔符处理(就像最初问题中的示例一样),因为长度函数将空格带在后面。
下面是他的代码,稍微调整了一下,允许使用空格分隔符:
CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value] FROM
(
SELECT
[Value] = LTRIM(RTRIM(SUBSTRING(@List, [Number],
CHARINDEX(@Delim, @List + @Delim, [Number]) - [Number])))
FROM (SELECT Number = ROW_NUMBER() OVER (ORDER BY name)
FROM sys.all_objects) AS x
WHERE Number <= LEN(@List)
AND SUBSTRING(@Delim + @List, [Number], LEN(@Delim+'x')-1) = @Delim
) AS y
);
下面的示例使用递归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
在我看来,你们把事情搞得太复杂了。只需要创建一个CLR UDF就可以了。
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
public partial class UserDefinedFunctions {
[SqlFunction]
public static SqlString SearchString(string Search) {
List<string> SearchWords = new List<string>();
foreach (string s in Search.Split(new char[] { ' ' })) {
if (!s.ToLower().Equals("or") && !s.ToLower().Equals("and")) {
SearchWords.Add(s);
}
}
return new SqlString(string.Join(" OR ", SearchWords.ToArray()));
}
};
通过delimeter函数得到字符串的n个部分:
create function GetStringPartByDelimeter (
@value as nvarchar(max),
@delimeter as nvarchar(max),
@position as int
) returns NVARCHAR(MAX)
AS BEGIN
declare @startPos as int
declare @endPos as int
set @endPos = -1
while (@position > 0 and @endPos != 0) begin
set @startPos = @endPos + 1
set @endPos = charindex(@delimeter, @value, @startPos)
if(@position = 1) begin
if(@endPos = 0)
set @endPos = len(@value) + 1
return substring(@value, @startPos, @endPos - @startPos)
end
set @position = @position - 1
end
return null
end
以及用法:
select dbo.GetStringPartByDelimeter ('a;b;c;d;e', ';', 3)
返回:
c
在这里我发布了一个简单的解决方法
CREATE FUNCTION [dbo].[split](
@delimited NVARCHAR(MAX),
@delimiter NVARCHAR(100)
) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
AS
BEGIN
DECLARE @xml XML
SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'
INSERT INTO @t(val)
SELECT r.value('.','varchar(MAX)') as item
FROM @xml.nodes('/t') as records(r)
RETURN
END
像这样执行函数
select * from dbo.split('Hello John Smith',' ')