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, ',')

几乎所有其他答案都是替换正在分割的字符串，这浪费了CPU周期并执行不必要的内存分配。

我在这里介绍了一种更好的进行字符串拆分的方法:http://www.digitalruby.com/split-string-sql-server/

代码如下:

SET NOCOUNT ON

-- You will want to change nvarchar(MAX) to nvarchar(50), varchar(50) or whatever matches exactly with the string column you will be searching against
DECLARE @SplitStringTable TABLE (Value nvarchar(MAX) NOT NULL)
DECLARE @StringToSplit nvarchar(MAX) = 'your|string|to|split|here'
DECLARE @SplitEndPos int
DECLARE @SplitValue nvarchar(MAX)
DECLARE @SplitDelim nvarchar(1) = '|'
DECLARE @SplitStartPos int = 1

SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)

WHILE @SplitEndPos > 0
BEGIN
    SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, (@SplitEndPos - @SplitStartPos))
    INSERT @SplitStringTable (Value) VALUES (@SplitValue)
    SET @SplitStartPos = @SplitEndPos + 1
    SET @SplitEndPos = CHARINDEX(@SplitDelim, @StringToSplit, @SplitStartPos)
END

SET @SplitValue = SUBSTRING(@StringToSplit, @SplitStartPos, 2147483647)
INSERT @SplitStringTable (Value) VALUES(@SplitValue)

SET NOCOUNT OFF

-- You can select or join with the values in @SplitStringTable at this point.

递归CTE解决方案与服务器疼痛，测试它

MS SQL Server 2008模式设置:

create table Course( Courses varchar(100) );
insert into Course values ('Hello John Smith');

查询1:

with cte as
   ( select 
        left( Courses, charindex( ' ' , Courses) ) as a_l,
        cast( substring( Courses, 
                         charindex( ' ' , Courses) + 1 , 
                         len(Courses ) ) + ' ' 
              as varchar(100) )  as a_r,
        Courses as a,
        0 as n
     from Course t
    union all
      select 
        left(a_r, charindex( ' ' , a_r) ) as a_l,
        substring( a_r, charindex( ' ' , a_r) + 1 , len(a_R ) ) as a_r,
        cte.a,
        cte.n + 1 as n
    from Course t inner join cte 
         on t.Courses = cte.a and len( a_r ) > 0

   )
select a_l, n from cte
--where N = 1

结果:

|    A_L | N |
|--------|---|
| Hello  | 0 |
|  John  | 1 |
| Smith  | 2 |

这个模式工作得很好，可以进行推广

Convert(xml,'<n>'+Replace(FIELD,'.','</n><n>')+'</n>').value('(/n[INDEX])','TYPE')
                          ^^^^^                                   ^^^^^     ^^^^

注意字段，索引和类型。

让一些表具有类似的标识符

sys.message.1234.warning.A45
sys.message.1235.error.O98
....

然后，你就可以写作了

SELECT Source         = q.value('(/n[1])', 'varchar(10)'),
       RecordType     = q.value('(/n[2])', 'varchar(20)'),
       RecordNumber   = q.value('(/n[3])', 'int'),
       Status         = q.value('(/n[4])', 'varchar(5)')
FROM   (
         SELECT   q = Convert(xml,'<n>'+Replace(fieldName,'.','</n><n>')+'</n>')
         FROM     some_TABLE
       ) Q

拆铸所有零件。

修改@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

建立在@NothingsImpossible解决方案上，或者，更确切地说，评论投票最多的答案(就在接受的答案下面)，我发现下面的快速和肮脏的解决方案满足了我自己的需求-它的好处是完全在SQL域内。

给定一个字符串"first;second;third;fourth;fifth"，比如说，我想获取第三个标记。只有当我们知道字符串将有多少标记时，这才有效——在这种情况下，它是5。所以我的操作方式是将最后两个令牌切掉(内部查询)，然后将前两个令牌切掉(外部查询)

我知道这是丑陋的，涵盖了我所处的具体情况，但我张贴它只是为了以防有人发现它有用。干杯

select 
    REVERSE(
        SUBSTRING(
            reverse_substring, 
            0, 
            CHARINDEX(';', reverse_substring)
        )
    ) 
from 
(
    select 
        msg,
        SUBSTRING(
            REVERSE(msg), 
            CHARINDEX(
                ';', 
                REVERSE(msg), 
                CHARINDEX(
                    ';',
                    REVERSE(msg)
                )+1
            )+1,
            1000
        ) reverse_substring
    from 
    (
        select 'first;second;third;fourth;fifth' msg
    ) a
) b