修改@Aaron Bertrand的功能

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255),
    @Idx int
)
RETURNS NVARCHAR(1000)
AS
BEGIN
    DECLARE @ValueTable TABLE(String NVARCHAR(50), Ind int)
    DECLARE @Value NVARCHAR(50)
    BEGIN
    INSERT INTO @ValueTable
    SELECT Value, idx FROM
        (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
          ) AS R WHERE idx = @Idx
    SET @Value = (SELECT String FROM @ValueTable)
    END
    RETURN @Value
END
GO

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

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

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

我开发了这个，

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