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

你可以在SQL用户定义函数解析带分隔符的字符串中找到有用的解决方案(来自代码项目)。

你可以使用这个简单的逻辑:

Declare @products varchar(200) = '1|20|3|343|44|6|8765'
Declare @individual varchar(20) = null

WHILE LEN(@products) > 0
BEGIN
    IF PATINDEX('%|%', @products) > 0
    BEGIN
        SET @individual = SUBSTRING(@products,
                                    0,
                                    PATINDEX('%|%', @products))
        SELECT @individual

        SET @products = SUBSTRING(@products,
                                  LEN(@individual + '|') + 1,
                                  LEN(@products))
    END
    ELSE
    BEGIN
        SET @individual = @products
        SET @products = NULL
        SELECT @individual
    END
END

    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

下面是一个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

修改@Aaron Bertrand的功能

CREATE FUNCTION [dbo].[SplitString]
(
    @List NVARCHAR(MAX),
    @Delim VARCHAR(255),
    @Idx int
)
RETURNS NVARCHAR(1000)
AS
BEGIN
    DECLARE @ValueTable TABLE(String NVARCHAR(50), Ind int)
    DECLARE @Value NVARCHAR(50)
    BEGIN
    INSERT INTO @ValueTable
    SELECT Value, idx FROM
        (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
          ) AS R WHERE idx = @Idx
    SET @Value = (SELECT String FROM @ValueTable)
    END
    RETURN @Value
END
GO

我在网上寻找解决方案，下面的工作对我来说。 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