试试这个:

CREATE function [SplitWordList]
(
 @list varchar(8000)
)
returns @t table 
(
 Word varchar(50) not null,
 Position int identity(1,1) not null
)
as begin
  declare 
    @pos int,
    @lpos int,
    @item varchar(100),
    @ignore varchar(100),
    @dl int,
    @a1 int,
    @a2 int,
    @z1 int,
    @z2 int,
    @n1 int,
    @n2 int,
    @c varchar(1),
    @a smallint
  select 
    @a1 = ascii('a'),
    @a2 = ascii('A'),
    @z1 = ascii('z'),
    @z2 = ascii('Z'),
    @n1 = ascii('0'),
    @n2 = ascii('9')
  set @ignore = '''"'
  set @pos = 1
  set @dl = datalength(@list)
  set @lpos = 1
  set @item = ''
  while (@pos <= @dl) begin
    set @c = substring(@list, @pos, 1)
    if (@ignore not like '%' + @c + '%') begin
      set @a = ascii(@c)
      if ((@a >= @a1) and (@a <= @z1))  
        or ((@a >= @a2) and (@a <= @z2))
        or ((@a >= @n1) and (@a <= @n2))
      begin
        set @item = @item + @c
      end else if (@item > '') begin
        insert into @t values (@item)
        set @item = ''
      end
    end 
    set @pos = @pos + 1
  end
  if (@item > '') begin
    insert into @t values (@item)
  end
  return
end

像这样测试它:

select * from SplitWordList('Hello John Smith')

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

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

select dbo.SplitAndReturnNth('Hello John Smith',' ',2)

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

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

    Alter Function dbo.fn_Split
    (
    @Expression nvarchar(max),
    @Delimiter  nvarchar(20) = ',',
    @Qualifier  char(1) = Null
    )
    RETURNS @Results TABLE (id int IDENTITY(1,1), value nvarchar(max))
    AS
    BEGIN
       /* USAGE
            Select * From dbo.fn_Split('apple pear grape banana orange honeydew cantalope 3 2 1 4', ' ', Null)
            Select * From dbo.fn_Split('1,abc,"Doe, John",4', ',', '"')
            Select * From dbo.fn_Split('Hello 0,"&""&&&&', ',', '"')
       */

       -- Declare Variables
       DECLARE
          @X     xml,
          @Temp  nvarchar(max),
          @Temp2 nvarchar(max),
          @Start int,
          @End   int

       -- HTML Encode @Expression
       Select @Expression = (Select @Expression For XML Path(''))

       -- Find all occurences of @Delimiter within @Qualifier and replace with |||***|||
       While PATINDEX('%' + @Qualifier + '%', @Expression) > 0 AND Len(IsNull(@Qualifier, '')) > 0
       BEGIN
          Select
             -- Starting character position of @Qualifier
             @Start = PATINDEX('%' + @Qualifier + '%', @Expression),
             -- @Expression starting at the @Start position
             @Temp = SubString(@Expression, @Start + 1, LEN(@Expression)-@Start+1),
             -- Next position of @Qualifier within @Expression
             @End = PATINDEX('%' + @Qualifier + '%', @Temp) - 1,
             -- The part of Expression found between the @Qualifiers
             @Temp2 = Case When @End < 0 Then @Temp Else Left(@Temp, @End) End,
             -- New @Expression
             @Expression = REPLACE(@Expression,
                                   @Qualifier + @Temp2 + Case When @End < 0 Then '' Else @Qualifier End,
                                   Replace(@Temp2, @Delimiter, '|||***|||')
                           )
       END

       -- Replace all occurences of @Delimiter within @Expression with '</fn_Split>&ltfn_Split>'
       -- And convert it to XML so we can select from it
       SET
          @X = Cast('&ltfn_Split>' +
                    Replace(@Expression, @Delimiter, '</fn_Split>&ltfn_Split>') +
                    '</fn_Split>' as xml)

       -- Insert into our returnable table replacing '|||***|||' back to @Delimiter
       INSERT @Results
       SELECT
          "Value" = LTRIM(RTrim(Replace(C.value('.', 'nvarchar(max)'), '|||***|||', @Delimiter)))
       FROM
          @X.nodes('fn_Split') as X(C)

       -- Return our temp table
       RETURN
    END

Aaron Bertrand的回答很好，但也有缺陷。它不能准确地将空格作为分隔符处理(就像最初问题中的示例一样)，因为长度函数将空格带在后面。

下面是他的代码，稍微调整了一下，允许使用空格分隔符:

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255)
)
RETURNS TABLE
AS
    RETURN ( SELECT [Value] FROM 
      ( 
        SELECT 
          [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+'x')-1) = @Delim
      ) AS y
    );