下面的示例使用递归CTE

更新18.09.2013

CREATE FUNCTION dbo.SplitStrings_CTE(@List nvarchar(max), @Delimiter nvarchar(1))
RETURNS @returns TABLE (val nvarchar(max), [level] int, PRIMARY KEY CLUSTERED([level]))
AS
BEGIN
;WITH cte AS
 (
  SELECT SUBSTRING(@List, 0, CHARINDEX(@Delimiter,  @List + @Delimiter)) AS val,
         CAST(STUFF(@List + @Delimiter, 1, CHARINDEX(@Delimiter, @List + @Delimiter), '') AS nvarchar(max)) AS stval, 
         1 AS [level]
  UNION ALL
  SELECT SUBSTRING(stval, 0, CHARINDEX(@Delimiter, stval)),
         CAST(STUFF(stval, 1, CHARINDEX(@Delimiter, stval), '') AS nvarchar(max)),
         [level] + 1
  FROM cte
  WHERE stval != ''
  )
  INSERT @returns
  SELECT REPLACE(val, ' ','' ) AS val, [level]
  FROM cte
  WHERE val > ''
  RETURN
END

演示SQLFiddle

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

SELECT 
    SUBSTRING(
                SUBSTRING('Hello John Smith' ,0,CHARINDEX(' ','Hello John Smith',CHARINDEX(' ','Hello John Smith')+1)
                        ),CHARINDEX(' ','Hello John Smith'),LEN('Hello John Smith')
            )

下面是一个函数，它将完成问题的目标，即分割字符串并访问项目X:

CREATE FUNCTION [dbo].[SplitString]
(
   @List       VARCHAR(MAX),
   @Delimiter  VARCHAR(255),
   @ElementNumber INT
)
RETURNS VARCHAR(MAX)
AS
BEGIN

       DECLARE @inp VARCHAR(MAX)
       SET @inp = (SELECT REPLACE(@List,@Delimiter,'_DELMTR_') FOR XML PATH(''))

       DECLARE @xml XML
       SET @xml = '<split><el>' + REPLACE(@inp,'_DELMTR_','</el><el>') + '</el></split>'

       DECLARE @ret VARCHAR(MAX)
       SET @ret = (SELECT
              el = split.el.value('.','varchar(max)')
       FROM  @xml.nodes('/split/el[string-length(.)>0][position() = sql:variable("@elementnumber")]') split(el))

       RETURN @ret

END

用法:

SELECT dbo.SplitString('Hello John Smith', ' ', 2)

结果:

John

如果任何人只想获得分离文本的一部分，可以使用这个

select * from from splitstringsep ('Word1 word2 word3'，' ')

CREATE function [dbo].[SplitStringSep] 
(
    @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
  )

这里的大多数解决方案使用while循环或递归cte。我保证，如果你可以使用空格以外的分隔符，基于集合的方法会更好:

CREATE FUNCTION [dbo].[SplitString]
    (
        @List NVARCHAR(MAX),
        @Delim VARCHAR(255)
    )
    RETURNS TABLE
    AS
        RETURN ( 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
        );

示例用法:

SELECT Value FROM dbo.SplitString('foo,bar,blat,foo,splunge',',')
  WHERE idx = 3;

结果:

----
blat

您还可以将需要的idx作为参数添加到函数中，但我将把它作为练习留给读者。

您不能仅使用SQL Server 2016中添加的本地STRING_SPLIT函数来实现这一点，因为不能保证输出将按照原始列表的顺序呈现。换句话说，如果你传递3,6,1结果可能是这个顺序，但它可能是1,3,6。我已经在这里请求社区的帮助来改进内置功能:

请帮助改进STRING_SPLIT

有了足够的定性反馈，他们可能会考虑做出以下改进:

STRING_SPLIT不是特性完整的

更多关于拆分函数，为什么(和证明)while循环和递归cte不能扩展，以及更好的替代方案，如果拆分字符串来自应用层:

以正确的方式拆分字符串-或者退而求其次的方式 拆分字符串:后续 分割字符串:现在使用更少的T-SQL 比较字符串分割/连接方法 处理一个整数列表:我的方法 拆分整数列表:另一个汇总 更多关于拆分列表的内容:自定义分隔符、防止重复和维护顺序 在SQL Server中删除字符串中的重复项

在SQL Server 2016或更高版本上，你应该看看STRING_SPLIT()和STRING_AGG():

性能惊喜和假设:STRING_SPLIT() STRING_SPLIT()在SQL Server 2016:后续#1 STRING_SPLIT()在SQL Server 2016:后续#2 SQL Server v.Next: STRING_AGG()性能 使用SQL Server的新STRING_AGG和STRING_SPLIT函数解决老问题

我不相信SQL Server有内置的分裂函数，所以除了UDF，我知道的唯一其他答案是劫持PARSENAME函数:

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 2) 

PARSENAME接受一个字符串，并根据句点分隔它。它接受一个数字作为第二个参数，该数字指定要返回字符串的哪一部分(从后到前工作)。

SELECT PARSENAME(REPLACE('Hello John Smith', ' ', '.'), 3)  --return Hello

明显的问题是当字符串已经包含句点时。我仍然认为使用UDF是最好的方式……还有其他建议吗?