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

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

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

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

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

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

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