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

用法不变。

这里有一个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','**')

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

基于纯集的解决方案，使用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中拆分字符串，而不需要函数:

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

我一直在使用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