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

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

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 2005中不起作用

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

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

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

select dbo.SplitAndReturnNth('Hello John 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

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