CREATE TABLE test(
    id int,
    adress varchar(100)
);
INSERT INTO test VALUES(1, 'Ludovic Aubert, 42 rue de la Victoire, 75009, Paris, France'),(2, 'Jose Garcia, 1 Calle de la Victoria, 56500 Barcelona, Espana');

SELECT id, value, COUNT(*) OVER (PARTITION BY id) AS n, ROW_NUMBER() OVER (PARTITION BY id ORDER BY (SELECT NULL)) AS rn, adress
FROM test
CROSS APPLY STRING_SPLIT(adress, ',')

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

这里的大多数解决方案使用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函数解决老问题

下面的示例使用递归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

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