我开发了这个，

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

    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

在这里我发布了一个简单的解决方法

CREATE FUNCTION [dbo].[split](
          @delimited NVARCHAR(MAX),
          @delimiter NVARCHAR(100)
        ) RETURNS @t TABLE (id INT IDENTITY(1,1), val NVARCHAR(MAX))
        AS
        BEGIN
          DECLARE @xml XML
          SET @xml = N'<t>' + REPLACE(@delimited,@delimiter,'</t><t>') + '</t>'

          INSERT INTO @t(val)
          SELECT  r.value('.','varchar(MAX)') as item
          FROM  @xml.nodes('/t') as records(r)
          RETURN
        END

像这样执行函数

  select * from dbo.split('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 Server有内置的分裂函数，所以除了UDF，我知道的唯一其他答案是劫持PARSENAME函数:

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

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

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

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

我在网上寻找解决方案，下面的工作对我来说。 Ref。

然后像这样调用函数:

SELECT * FROM dbo.split('ram shyam hari gopal',' ')

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE FUNCTION [dbo].[Split](@String VARCHAR(8000), @Delimiter CHAR(1))       
RETURNS @temptable TABLE (items VARCHAR(8000))       
AS       
BEGIN       
    DECLARE @idx INT       
    DECLARE @slice VARCHAR(8000)        
    SELECT @idx = 1       
    IF len(@String)<1 OR @String IS NULL  RETURN       
    WHILE @idx!= 0       
    BEGIN       
        SET @idx = charindex(@Delimiter,@String)       
        IF @idx!=0       
            SET @slice = LEFT(@String,@idx - 1)       
        ELSE       
            SET @slice = @String       
        IF(len(@slice)>0)  
            INSERT INTO @temptable(Items) VALUES(@slice)       
        SET @String = RIGHT(@String,len(@String) - @idx)       
        IF len(@String) = 0 break       
    END   
    RETURN       
END