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

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

拆铸所有零件。

我一直在使用vzczc的答案使用递归cte的一段时间，但一直想更新它来处理可变长度分隔符，也处理字符串与前驱和滞后“分隔符”，如当你有一个csv文件的记录，如:

“鲍勃”,“史密斯”桑尼维尔”,“CA”

或者当你处理如下所示的六部分fqn时。我广泛地使用这些来记录subject_fqn的审计，错误处理等，parsename只处理四个部分:

[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]

这是我的更新版本，感谢vzczc的原始帖子!

select * from [utility].[split_string](N'"this"."string"."gets"."split"."and"."removes"."leading"."and"."trailing"."quotes"', N'"."', N'"', N'"');

select * from [utility].[split_string](N'"this"."string"."gets"."split"."but"."leaves"."leading"."and"."trailing"."quotes"', N'"."', null, null);

select * from [utility].[split_string](N'[netbios_name].[machine_name].[instance].[database].[schema].[table].[column]', N'].[', N'[', N']');

create function [utility].[split_string] ( 
  @input       [nvarchar](max) 
  , @separator [sysname] 
  , @lead      [sysname] 
  , @lag       [sysname]) 
returns @node_list table ( 
  [index]  [int] 
  , [node] [nvarchar](max)) 
  begin 
      declare @separator_length [int]= len(@separator) 
              , @lead_length    [int] = isnull(len(@lead), 0) 
              , @lag_length     [int] = isnull(len(@lag), 0); 
      -- 
      set @input = right(@input, len(@input) - @lead_length); 
      set @input = left(@input, len(@input) - @lag_length); 
      -- 
      with [splitter]([index], [starting_position], [start_location]) 
           as (select cast(@separator_length as [bigint]) 
                      , cast(1 as [bigint]) 
                      , charindex(@separator, @input) 
               union all 
               select [index] + 1 
                      , [start_location] + @separator_length 
                      , charindex(@separator, @input, [start_location] + @separator_length) 
               from   [splitter] 
               where  [start_location] > 0) 
      -- 
      insert into @node_list 
                  ([index],[node]) 
        select [index] - @separator_length                   as [index] 
               , substring(@input, [starting_position], case 
                                                            when [start_location] > 0 
                                                                then 
                                                              [start_location] - [starting_position] 
                                                            else 
                                                              len(@input) 
                                                        end) as [node] 
        from   [splitter]; 
      -- 
      return; 
  end; 
go 

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

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

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

这里有一个UDF可以做到这一点。它将返回一个带分隔符的值的表，我还没有尝试所有的场景，但您的示例工作良好。

CREATE FUNCTION SplitString 
(
    -- Add the parameters for the function here
    @myString varchar(500),
    @deliminator varchar(10)
)
RETURNS 
@ReturnTable TABLE 
(
    -- Add the column definitions for the TABLE variable here
    [id] [int] IDENTITY(1,1) NOT NULL,
    [part] [varchar](50) NULL
)
AS
BEGIN
        Declare @iSpaces int
        Declare @part varchar(50)

        --initialize spaces
        Select @iSpaces = charindex(@deliminator,@myString,0)
        While @iSpaces > 0

        Begin
            Select @part = substring(@myString,0,charindex(@deliminator,@myString,0))

            Insert Into @ReturnTable(part)
            Select @part

    Select @myString = substring(@mystring,charindex(@deliminator,@myString,0)+ len(@deliminator),len(@myString) - charindex(' ',@myString,0))


            Select @iSpaces = charindex(@deliminator,@myString,0)
        end

        If len(@myString) > 0
            Insert Into @ReturnTable
            Select @myString

    RETURN 
END
GO

你可以这样称呼它:

Select * From SplitString('Hello John Smith',' ')

编辑:使用len>1处理分隔符的更新解决方案如下:

select * From SplitString('Hello**John**Smith','**')

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