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

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

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

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

拆铸所有零件。

从SQL Server 2016开始，我们使用string_split

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

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

以下是我的解决方案，可能会对某些人有所帮助。修改以上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
)