如何分割带分隔符的字符串，以便可以访问单个项?

小开

最佳答案

你可以在解析分隔字符串的SQL用户定义函数中找到有用的解决方案(来自代码项目)。

你可以使用这个简单的逻辑:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null


WHILE LEN(@products) > 0
BEGIN
IF PATINDEX('%|%', @products) > 0
BEGIN
SET @individual = SUBSTRING(@products,
0,
PATINDEX('%|%', @products))
SELECT @individual


SET @products = SUBSTRING(@products,
LEN(@individual + '|') + 1,
LEN(@products))
END
ELSE
BEGIN
SET @individual = @products
SET @products = NULL
SELECT @individual
END
END

小开

好吧，我的代码并不那么简单，但下面是我用来将逗号分隔的输入变量分割为单个值，并将其放入表变量中的代码。我相信您可以稍微修改一下，根据空格进行分割，然后对该表变量执行基本的SELECT查询以获得结果。

-- Create temporary table to parse the list of accounting cycles.
DECLARE @tblAccountingCycles table
(
AccountingCycle varchar(10)
)


DECLARE @vchAccountingCycle varchar(10)
DECLARE @intPosition int


SET @vchAccountingCycleIDs = LTRIM(RTRIM(@vchAccountingCycleIDs)) + ','
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)


IF REPLACE(@vchAccountingCycleIDs, ',', '') <> ''
BEGIN
WHILE @intPosition > 0
BEGIN
SET @vchAccountingCycle = LTRIM(RTRIM(LEFT(@vchAccountingCycleIDs, @intPosition - 1)))
IF @vchAccountingCycle <> ''
BEGIN
INSERT INTO @tblAccountingCycles (AccountingCycle) VALUES (@vchAccountingCycle)
END
SET @vchAccountingCycleIDs = RIGHT(@vchAccountingCycleIDs, LEN(@vchAccountingCycleIDs) - @intPosition)
SET @intPosition = CHARINDEX(',', @vchAccountingCycleIDs, 1)
END
END

概念是差不多的。另一种选择是利用SQL Server 2005本身的. net兼容性。实际上，您可以在. net中编写一个简单的方法，将字符串分割，然后将其作为存储过程/函数公开。

小开

这里有一个UDF可以做到这一点。它将返回一个带分隔符的值的表，我还没有尝试所有的场景，但您的示例工作良好。


CREATE FUNCTION SplitString
(
-- Add the parameters for the function here
@myString varchar(500),
@deliminator varchar(10)
)
RETURNS
@ReturnTable TABLE
(
-- Add the column definitions for the TABLE variable here
[id] [int] IDENTITY(1,1) NOT NULL,
[part] [varchar](50) NULL
)
AS
BEGIN
Declare @iSpaces int
Declare @part varchar(50)


--initialize spaces
Select @iSpaces = charindex(@deliminator,@myString,0)
While @iSpaces > 0


Begin
Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))


Insert Into @ReturnTable(part)
Select @part


Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))




Select @iSpaces = charindex(@deliminator,@myString,0)
end


If len(@myString) > 0
Insert Into @ReturnTable
Select @myString


RETURN
END
GO

你可以这样称呼它:


Select * From SplitString('Hello John Smith',' ')

编辑:使用len>1处理分隔符的更新解决方案如下:


select * From SplitString('Hello**John**Smith','**')

小开

试试这个:

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')

小开

我不相信SQL Server有内置的分裂函数，所以除了UDF，我知道的唯一其他答案是劫持PARSENAME函数:

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2)

PARSENAME接受一个字符串，并根据句点分隔它。它接受一个数字作为第二个参数，该数字指定要返回字符串的哪一部分(从后到前工作)。

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3)  --return Hello

明显的问题是当字符串已经包含句点时。我仍然认为使用UDF是最好的方式……还有其他建议吗?

小开

首先，创建一个函数(使用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

用法不变。

小开

可以利用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万行表上调用函数并不是最好的主意。如果将字符串拆分到多行，我会避免使用该函数。

小开

我在网上寻找解决方案，下面对我有用。裁判 . < / p >

然后像这样调用函数:

SELECT * FROM dbo.split('ram shyam hari gopal',' ')

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))
RETURNS @temptable TABLE (items VARCHAR(8000))
AS
BEGIN
DECLARE @idx INT
DECLARE @slice VARCHAR(8000)
SELECT @idx = 1
IF len(@String)<1 OR @String IS NULL  RETURN
WHILE @idx!= 0
BEGIN
SET @idx = charindex(@Delimiter,@String)
IF @idx!=0
SET @slice = LEFT(@String,@idx - 1)
ELSE
SET @slice = @String
IF(len(@slice)>0)
INSERT INTO @temptable(Items) VALUES(@slice)
SET @String = RIGHT(@String,len(@String) - @idx)
IF len(@String) = 0 break
END
RETURN
END

小开

在我看来，你们把事情搞得太复杂了。只需要创建一个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()));
}
};

小开

我知道这是一个老问题，但我认为有人可以从我的解决方案中受益。

select
SUBSTRING(column_name,1,CHARINDEX(' ',column_name,1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
,1
,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)-1)
,SUBSTRING(SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name))
,CHARINDEX(' ',SUBSTRING(column_name,CHARINDEX(' ',column_name,1)+1,LEN(column_name)),1)+1
,LEN(column_name))
from table_name

< a href = " http://www.sqlfiddle.com/ !3/776f3/1" rel="nofollow noreferrer">SQL提琴

优点:

它用' '分隔所有3个子字符串。
不能使用while循环，因为它会降低性能。
不需要Pivot，因为所有生成的子字符串将显示在李一行< / >

限制:

一个人必须知道绝对的不。Of Spaces(子字符串)。

请注意:解决方案可以给出最多到N的子字符串。

为了克服这个限制，我们可以使用下面的< >强ref < / >强。

但是上面的< >强解< / >强不能在表中使用(实际上我不能使用它)。

我希望这个解决方案能帮助到一些人。

在记录> 50000的情况下，使用LOOPS不是明智的，因为它将降级性能

小开

在这里我发布了一个简单的解决方法

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

< p > < br > 执行如下函数

  select * from dbo.split('Hello John Smith',' ')

小开

使用string和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

小开

下面的示例使用递归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
)

小开

我使用弗雷德里克的答案，但这在SQL Server 2005中不起作用

我修改了它，我使用select和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

小开




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

小开

这里的大多数解决方案使用while循环或递归cte。我保证，如果你可以使用空格以外的分隔符，基于集合的方法会更好:

CREATE FUNCTION [dbo].[SplitString]
(
@List NVARCHAR(MAX),
@Delim VARCHAR(255)
)
RETURNS TABLE
AS
RETURN ( SELECT [Value], idx = RANK() OVER (ORDER BY n) FROM
(
SELECT n = Number,
[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)) = @Delim
) AS y
);

示例用法:

SELECT Value FROM dbo.SplitString('foo,bar,blat,foo,splunge',',')
WHERE idx = 3;

结果:

----
blat

你也可以将你想要的idx作为参数添加到函数中，但我将把它作为练习留给读者。

你不能用SQL Server 2016中添加的只是 (本机STRING_SPLIT函数)这样做，因为不能保证输出将按照原始列表的顺序呈现。换句话说，如果传入3,6,1，结果可能是这个顺序，但可以是1,3,6。我已经在这里请求社区的帮助来改进内置功能:

请帮助STRING_SPLIT改进 < / > < /李>

有了足够的定性反馈，他们实际上可能会考虑进行以下一些增强:

STRING_SPLIT is not feature complete

更多关于拆分函数，为什么(和证明)while循环和递归cte不能扩展，以及更好的替代方案，如果拆分字符串来自应用层:

在SQL Server 2016或更高版本上，你应该查看STRING_SPLIT()和STRING_AGG():

小开

递归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 href = " http://sqlfiddle.com/ !3/9ebaa / 1/0 " rel = " nofollow”结果> < / >:

|    A_L | N |
|--------|---|
| Hello  | 0 |
|  John  | 1 |
| Smith  | 2 |

小开

下面是一个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

小开

一个简单的优化算法:

ALTER FUNCTION [dbo].[Split]( @Text NVARCHAR(200),@Splitor CHAR(1) )
RETURNS @Result TABLE ( value NVARCHAR(50))
AS
BEGIN
DECLARE @PathInd INT
Set @Text+=@Splitor
WHILE LEN(@Text) > 0
BEGIN
SET @PathInd=PATINDEX('%'+@Splitor+'%',@Text)
INSERT INTO  @Result VALUES(SUBSTRING(@Text, 0, @PathInd))
SET @Text= SUBSTRING(@Text, @PathInd+1, LEN(@Text))
END
RETURN
END

小开

我一直在使用vzczc的答案使用递归cte的一段时间，但一直想更新它来处理可变长度分隔符，也处理字符串与前驱和滞后“分隔符”，如当你有一个csv文件的记录，如:

“鲍勃”、“史密斯”,“森尼维耳市”、“CA”

或者当你处理如下所示的六部分fqn时。我广泛地使用这些来记录subject_fqn的审计，错误处理等，parsename只处理四个部分:

[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]

这是我的更新版本，感谢vzczc的原始帖子!

select * from [utility].[split_string](N'"this"."string"."gets"."split"."and"."removes"."leading"."and"."trailing"."quotes"', N'"."', N'"', N'"');


select * from [utility].[split_string](N'"this"."string"."gets"."split"."but"."leaves"."leading"."and"."trailing"."quotes"', N'"."', null, null);


select * from [utility].[split_string](N'[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]', N'].[', N'[', N']');


create function [utility].[split_string] (
@input       [nvarchar](max)
, @separator [sysname]
, @lead      [sysname]
, @lag       [sysname])
returns @node_list table (
[index]  [int]
, [node] [nvarchar](max))
begin
declare @separator_length [int]= len(@separator)
, @lead_length    [int] = isnull(len(@lead), 0)
, @lag_length     [int] = isnull(len(@lag), 0);
--
set @input = right(@input, len(@input) - @lead_length);
set @input = left(@input, len(@input) - @lag_length);
--
with [splitter]([index], [starting_position], [start_location])
as (select cast(@separator_length as [bigint])
, cast(1 as [bigint])
, charindex(@separator, @input)
union all
select [index] + 1
, [start_location] + @separator_length
, charindex(@separator, @input, [start_location] + @separator_length)
from   [splitter]
where  [start_location] > 0)
--
insert into @node_list
([index],[node])
select [index] - @separator_length                   as [index]
, substring(@input, [starting_position], case
when [start_location] > 0
then
[start_location] - [starting_position]
else
len(@input)
end) as [node]
from   [splitter];
--
return;
end;
go

小开

几乎所有其他答案都是替换正在分割的字符串，这浪费了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.

小开

这个模式工作得很好，可以进行推广

Convert(xml,'<n>'+Replace(FIELD,'.','</n><n>')+'</n>').value('(/n[INDEX])','TYPE')
^^^^^                                   ^^^^^     ^^^^

注意场，指数和类型。

让一些表具有类似的标识符

sys.message.1234.warning.A45
sys.message.1235.error.O98
....

然后，你就可以写作了

SELECT Source         = q.value('(/n[1])', 'varchar(10)'),
RecordType     = q.value('(/n[2])', 'varchar(20)'),
RecordNumber   = q.value('(/n[3])', 'int'),
Status         = q.value('(/n[4])', 'varchar(5)')
FROM   (
SELECT   q = Convert(xml,'<n>'+Replace(fieldName,'.','</n><n>')+'</n>')
FROM     some_TABLE
) Q

拆铸所有零件。

小开

虽然类似于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分跑

小开

CREATE FUNCTION [dbo].[fnSplitString]
(
@string NVARCHAR(MAX),
@delimiter CHAR(1)
)
RETURNS @output TABLE(splitdata NVARCHAR(MAX)
)
BEGIN
DECLARE @start INT, @end INT
SELECT @start = 1, @end = CHARINDEX(@delimiter, @string)
WHILE @start < LEN(@string) + 1 BEGIN
IF @end = 0
SET @end = LEN(@string) + 1


INSERT INTO @output (splitdata)
VALUES(SUBSTRING(@string, @start, @end - @start))
SET @start = @end + 1
SET @end = CHARINDEX(@delimiter, @string, @start)


END
RETURN
END

并使用它

select *from dbo.fnSplitString('Querying SQL Server','')

小开

基于纯集的解决方案，使用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

小开

如果任何人只想获得分离文本的一部分，可以使用这个

select * from from splitstringsep ('Word1 word2 word3'，' ')

CREATE function [dbo].[SplitStringSep]
(
@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
)

小开

我开发了这个，

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的末尾就应该在这里。

小开

你可以在SQL中拆分字符串，而不需要函数:

DECLARE @bla varchar(MAX)
SET @bla = 'BED40DFC-F468-46DD-8017-00EF2FA3E4A4,64B59FC5-3F4D-4B0E-9A48-01F3D4F220B0,A611A108-97CA-42F3-A2E1-057165339719,E72D95EA-578F-45FC-88E5-075F66FD726C'


-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT
x.XmlCol.value('.', 'varchar(36)') AS val
FROM
(
SELECT
CAST('<e>' + REPLACE(@bla, ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

如果需要支持任意字符串(带有xml特殊字符)

DECLARE @bla NVARCHAR(MAX)
SET @bla = '<html>unsafe & safe Utf8CharsDon''tGetEncoded ÄöÜ - "Conex"<html>,Barnes & Noble,abc,def,ghi'


-- http://stackoverflow.com/questions/14712864/how-to-query-values-from-xml-nodes
SELECT
x.XmlCol.value('.', 'nvarchar(MAX)') AS val
FROM
(
SELECT
CAST('<e>' + REPLACE((SELECT @bla FOR XML PATH('')), ',', '</e><e>') + '</e>' AS xml) AS RawXml
) AS b
CROSS APPLY b.RawXml.nodes('e') x(XmlCol);

小开

通过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)

返回:

小开

这个问题是不是关于字符串分割方法，但关于如何得到第n个元素。

这里所有的答案都是使用递归，__abc0，多个CHARINDEX， REVERSE和PATINDEX进行某种字符串分割，发明函数，调用CLR方法，数字表，__abc4…大多数答案都包含了很多行代码。

但是-如果你真的只想要得到第n个元素的方法 -这可以作为真正的小笑话来完成，没有UDF，甚至没有子选择…作为一个额外的好处:类型安全

用空格分隔第2部分:

DECLARE @input NVARCHAR(100)=N'part1 part2 part3';
SELECT CAST(N'<x>' + REPLACE(@input,N' ',N'</x><x>') + N'</x>' AS XML).value('/x[2]','nvarchar(max)')

当然，你可以使用变量用于分隔符和位置(使用sql:column直接从查询值中检索位置):

DECLARE @dlmt NVARCHAR(10)=N' ';
DECLARE @pos INT = 2;
SELECT CAST(N'<x>' + REPLACE(@input,@dlmt,N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)')

如果你的字符串可能包括禁止字符(尤其是&><中的一个)，你仍然可以这样做。只需首先在字符串上使用FOR XML PATH，隐式地将所有禁止的字符替换为合适的转义序列。

这是一个非常特殊的情况，如果- addition - 分隔符是分号。在这种情况下，我首先将分隔符替换为“#DLMT#”，最后将其替换为XML标记:

SET @input=N'Some <, > and &;Other äöü@€;One more';
SET @dlmt=N';';
SELECT CAST(N'<x>' + REPLACE((SELECT REPLACE(@input,@dlmt,'#DLMT#') AS [*] FOR XML PATH('')),N'#DLMT#',N'</x><x>') + N'</x>' AS XML).value('/x[sql:variable("@pos")][1]','nvarchar(max)');

更新SQL-Server 2016+

遗憾的是，开发人员忘记用STRING_SPLIT返回该部件的索引。但是，使用SQL-Server 2016+，有JSON_VALUE和OPENJSON。

使用JSON_VALUE，我们可以传入位置作为下标数组。

对于OPENJSON，文档明确表示:

当OPENJSON解析JSON数组时，该函数将JSON文本中元素的索引作为键返回。

像1,2,3这样的字符串只需要括号:
像this is an example这样的字符串必须是["this","is","an","example"]。
这些都是非常简单的字符串操作。试一下:

DECLARE @str VARCHAR(100)='Hello John Smith';
DECLARE @position INT = 2;


--We can build the json-path '$[1]' using CONCAT
SELECT JSON_VALUE('["' + REPLACE(@str,' ','","') + '"]',CONCAT('$[',@position-1,']'));

——查看这里的位置安全字符串分配器(从零开始的):

SELECT  JsonArray.[key] AS [Position]
,JsonArray.[value] AS [Part]
FROM OPENJSON('["' + REPLACE(@str,' ','","') + '"]') JsonArray

在这篇文章中，我测试了各种方法，发现OPENJSON真的很快。甚至比著名的“delimitedSplit8k()”快得多;方法……

更新2 -获取类型安全的值

我们可以通过使用双[[]]来使用数组中的数组。这允许类型化的WITH-clause子句:

DECLARE  @SomeDelimitedString VARCHAR(100)='part1|1|20190920';


DECLARE @JsonArray NVARCHAR(MAX)=CONCAT('[["',REPLACE(@SomeDelimitedString,'|','","'),'"]]');


SELECT @SomeDelimitedString          AS TheOriginal
,@JsonArray                    AS TransformedToJSON
,ValuesFromTheArray.*
FROM OPENJSON(@JsonArray)
WITH(TheFirstFragment  VARCHAR(100) '$[0]'
,TheSecondFragment INT          '$[1]'
,TheThirdFragment  DATE         '$[2]') ValuesFromTheArray

小开

declare @strng varchar(max)='hello john smith'
select (
substring(
@strng,
charindex(' ', @strng) + 1,
(
(charindex(' ', @strng, charindex(' ', @strng) + 1))
- charindex(' ',@strng)
)
))

小开

建立在@NothingsImpossible解决方案上，或者，更确切地说，评论投票最多的答案(就在接受的答案下面)，我发现下面的快速上手解决方案满足了我自己的需求-它的好处是完全在SQL域内。

给定一个字符串"first;second;third;fourth;fifth"，比如说，我想获取第三个标记。只有当我们知道字符串将有多少标记时，这才有效——在这种情况下，它是5。所以我的操作方式是将最后两个令牌切掉(内部查询)，然后将前两个令牌切掉(外部查询)

我知道这是丑陋的，涵盖了我所处的具体情况，但我张贴它只是为了以防有人发现它有用。干杯

select
REVERSE(
SUBSTRING(
reverse_substring,
0,
CHARINDEX(';', reverse_substring)
)
)
from
(
select
msg,
SUBSTRING(
REVERSE(msg),
CHARINDEX(
';',
REVERSE(msg),
CHARINDEX(
';',
REVERSE(msg)
)+1
)+1,
1000
) reverse_substring
from
(
select 'first;second;third;fourth;fifth' msg
) a
) b

小开

从SQL Server 2016开始，我们string_split

DECLARE @string varchar(100) = 'Richard, Mike, Mark'


SELECT value FROM string_split(@string, ',')

小开

使用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个元素。

小开

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
);

小开

如果数据库的兼容性级别为130或更高，则可以使用STRING_SPLIT函数和抵消取回子句按索引获取特定项。

要获取指数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';

小开

下面是一个函数，它将完成问题的目标，即分割字符串并访问项目X:

CREATE FUNCTION [dbo].[SplitString]
(
@List       VARCHAR(MAX),
@Delimiter  VARCHAR(255),
@ElementNumber INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN


DECLARE @inp VARCHAR(MAX)
SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH(''))


DECLARE @xml XML
SET @xml = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'


DECLARE @ret VARCHAR(MAX)
SET @ret = (SELECT
el = split.el.value('.','varchar(max)')
FROM  @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el))


RETURN @ret


END

用法:

SELECT dbo.SplitString('Hello John Smith', ' ', 2)

结果:

John

小开

解析姓和名的简单解决方案

DECLARE @Name varchar(10) = 'John Smith'


-- Get First Name
SELECT SUBSTRING(@Name, 0, (SELECT CHARINDEX(' ', @Name)))


-- Get Last Name
SELECT SUBSTRING(@Name, (SELECT CHARINDEX(' ', @Name)) + 1, LEN(@Name))

在我的例子中(在许多其他人中似乎也是如此……)，我有一个由一个空格隔开的姓和名列表。可以直接在选择语句中使用它来解析姓和名。

-- i.e. Get First and Last Name from a table of Full Names
SELECT SUBSTRING(FullName, 0, (SELECT CHARINDEX(' ', FullName))) as FirstName,
SUBSTRING(FullName, (SELECT CHARINDEX(' ', FullName)) + 1, LEN(FullName)) as LastName,
From FullNameTable

小开

我知道已经晚了，但我最近有了这个需求，并提出了下面的代码。我没有选择使用用户定义的函数。希望这能有所帮助。

SELECT
SUBSTRING(
SUBSTRING('Hello John Smith' ,0,CHARINDEX(' ','Hello John Smith',CHARINDEX(' ','Hello John Smith')+1)
),CHARINDEX(' ','Hello John Smith'),LEN('Hello John Smith')
)

小开

我知道这是一个非常老的问题，但从SQL Server 2016开始，有一些用于解析JSON数据的函数，可以专门用于解决OP的问题——而无需分割字符串或求助于用户定义的函数。要访问分隔字符串的特定索引处的项，请使用JSON_VALUE函数。然而，正确格式化的JSON数据是必需的:字符串必须用双引号"括起来，分隔符必须是逗号,，整个字符串用方括号[]括起来。

DECLARE @SampleString NVARCHAR(MAX) = '"Hello John Smith"';
--Format as JSON data.
SET @SampleString = '[' + REPLACE(@SampleString, ' ', '","') + ']';
SELECT
JSON_VALUE(@SampleString, '$[0]') AS Element1Value,
JSON_VALUE(@SampleString, '$[1]') AS Element2Value,
JSON_VALUE(@SampleString, '$[2]') AS Element3Value;

输出

Element1Value         Element2Value       Element3Value
--------------------- ------------------- ------------------------------
Hello                 John                Smith


(1 row affected)

小开

使用SQL Server 2016及以上版本。使用这段代码修剪字符串，忽略NULL值，并按正确的顺序应用行索引。它也适用于空格分隔符:

DECLARE @STRING_VALUE NVARCHAR(MAX) = 'one, two,,three, four,     five'


SELECT ROW_NUMBER() OVER (ORDER BY R.[index]) [index], R.[value] FROM
(
SELECT
1 [index], NULLIF(TRIM([value]), '') [value] FROM STRING_SPLIT(@STRING_VALUE, ',') T
WHERE
NULLIF(TRIM([value]), '') IS NOT NULL
) R

小开

如果你查看下面关于使用SQL分割字符串的SQL教程，你会发现许多函数可用于分割SQL Server上的给定字符串

例如，SplitAndReturnNth UDF函数可用于使用分隔符分割文本，并返回第n段作为函数的输出

select dbo.SplitAndReturnNth('Hello John Smith',' ',2)

小开

CREATE TABLE test(
id int,
adress varchar(100)
);
INSERT INTO test VALUES(1, 'Ludovic Aubert, 42 rue de la Victoire, 75009, Paris, France'),(2, 'Jose Garcia, 1 Calle de la Victoria, 56500 Barcelona, Espana');


SELECT id, value, COUNT(*) OVER (PARTITION BY id) AS n, ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS rn, adress
FROM test
CROSS APPLY STRING_SPLIT(adress, ',')

小开

在Azure SQL数据库(基于Microsoft SQL Server但不完全相同的东西)中，STRING_SPLIT函数的签名看起来像:

STRING_SPLIT ( string , separator [ , enable_ordinal ] )

当enable_ordinal标志被设置为1时，结果将包括一个名为ordinal的列，该列由输入字符串中子字符串的基于1的位置组成:

SELECT *
FROM STRING_SPLIT('hello john smith', ' ', 1)


| value | ordinal |
|-------|---------|
| hello | 1       |
| john  | 2       |
| smith | 3       |

这允许我们这样做:

SELECT value
FROM STRING_SPLIT('hello john smith', ' ', 1)
WHERE ordinal = 2


| value |
|-------|
| john  |

如果enable_ordinal不可用，则有一个技巧，假设输入字符串中的子字符串是唯一的。在这种情况下，CHAR_INDEX可用于查找子字符串在输入字符串中的位置:

SELECT value, ROW_NUMBER() OVER (ORDER BY CHARINDEX(value, input_str)) AS ord_pos FROM (VALUES ('hello john smith') ) AS x(input_str) CROSS APPLY STRING_SPLIT(input_str, ' ') | value | ord_pos | |-------+---------| | hello | 1 | | john | 2 | | smith | 3 |