拆分值对并使用 UDF 创建表

我一直在尝试编写一个表值函数，它将值对作为参数并返回一个包含两列的表。

下面是我想要做的函数签名。

FUNCTION [dbo].[ValuePairParser]( @DelimitedValuePairs VARCHAR(MAX),
                                  @Delimiter CHAR(1), 
                                  @ValuePairDelimiter CHAR(1) ) 
  RETURNS @ValuePairTable
  TABLE ( Id INT, Code INT ) 

我想调用如下方法

@ValuePairs VARCHAR(MAX) = '1:1, 1:2, 1:4, 2:3, 1000:230, 130:120,'

ValuePairParser (@ValuePairs, ',', ':')

您能看到任何分割上面的值对字符串并创建一个包含两列的表的好方法吗？

十年后回顾，今天肯定有更好的方法来做到这一点。

SQL Server 2022、Azure SQL 数据库、托管实例

(example)

CREATE OR ALTER FUNCTION dbo.SplitWithPairs
(
    @List           nvarchar(max),
    @MajorDelimiter varchar(3) = ',',
    @MinorDelimiter varchar(3) = ':'
)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN 
  (
    SELECT LeftItem = [1], RightItem = [2], Position
    FROM
    (
      SELECT Position = o.ordinal, 
             value    = TRIM(i.value),
             i.ordinal
        FROM        STRING_SPLIT(@List,   @MajorDelimiter, 1) AS o
        CROSS APPLY STRING_SPLIT(o.value, @MinorDelimiter, 1) AS i
      WHERE o.value > ''
    ) AS s PIVOT (MAX(value) FOR ordinal IN ([1],[2])) AS p
  );

SQL Server 2016 - 2019

(example)

在 SQL Server 2016 到 2019 中，进行了一些更改：

• STRING_SPLIT在 SQL Server 2022 之前不支持序数，因此我们可以使用OPENJSON instead
• 我们需要使用LTRIM/RTRIM since TRIM在 SQL Server 2017 中添加
• key是从 0 开始的，所以我们增加 1 以获得等效的从 1 开始的位置

关于此功能的一个注意事项是您不能使用,作为次定界符而不改变函数，因为, is how OPENJSON划定界限。您可能希望在这两个分隔符中保留其他字符。

CREATE OR ALTER FUNCTION dbo.SplitWithPairs
(
    @List           nvarchar(max),
    @MajorDelimiter varchar(3) = ',',
    @MinorDelimiter varchar(3) = ':' -- can't be ,
)
RETURNS TABLE WITH SCHEMABINDING
AS
  RETURN 
  (
    SELECT LeftItem = [1], RightItem = [2], Position
    FROM
    (  
      SELECT Position = o.[key] + 1, 
             value    = LTRIM(RTRIM(i.value)),
             ordinal  = i.[key] + 1
      FROM OPENJSON
      (
        CONCAT('["', REPLACE(STRING_ESCAPE(@List, 'JSON'), 
          @MajorDelimiter, '","'), '"]')
      ) AS o
      CROSS APPLY OPENJSON
      (
        REPLACE(CONCAT('[', QUOTENAME(o.value, '"'), ']'), 
          @MinorDelimiter, '","')
      ) AS i
      WHERE o.value > ''
    ) AS s PIVOT (MAX(value) FOR ordinal IN ([1],[2])) AS p
  );

不再支持的版本

最后，原始答案适用于早于 SQL Server 2016 的版本，我将保持原样，但免责声明它可以改进（不乏阅读有关该内容的文章）here而且，一般来说，多语句表值函数（尤其是循环）是一个很大的危险信号）：

CREATE FUNCTION [dbo].[SplitWithPairs]
(
    @List NVARCHAR(MAX),
    @MajorDelimiter VARCHAR(3) = ',',
    @MinorDelimiter VARCHAR(3) = ':'
)
RETURNS @Items TABLE
(
    Position  INT IDENTITY(1,1) NOT NULL,
    LeftItem  INT NOT NULL,
    RightItem INT NOT NULL
)
AS
BEGIN
    DECLARE
        @Item      NVARCHAR(MAX),
        @LeftItem  NVARCHAR(MAX),
        @RightItem NVARCHAR(MAX),
        @Pos       INT;

    SELECT
        @List = @List + ' ',
        @MajorDelimiter = LTRIM(RTRIM(@MajorDelimiter)),
        @MinorDelimiter = LTRIM(RTRIM(@MinorDelimiter));

    WHILE LEN(@List) > 0
    BEGIN
        SET @Pos = CHARINDEX(@MajorDelimiter, @List);

        IF @Pos = 0 
            SET @Pos = LEN(@List) + LEN(@MajorDelimiter);

        SELECT
            @Item = LTRIM(RTRIM(LEFT(@List, @Pos - 1))),
            @LeftItem = LTRIM(RTRIM(LEFT(@Item,
            CHARINDEX(@MinorDelimiter, @Item) - 1))),
            @RightItem = LTRIM(RTRIM(SUBSTRING(@Item,
            CHARINDEX(@MinorDelimiter, @Item)
            + LEN(@MinorDelimiter), LEN(@Item))));

        INSERT @Items(LeftItem, RightItem)
            SELECT @LeftItem, @RightItem;

        SET @List = SUBSTRING(@List,
            @Pos + LEN(@MajorDelimiter), DATALENGTH(@List));
    END
    RETURN;
END
GO

DECLARE @ValuePairs VARCHAR(MAX) = '1:1, 1:2, 1:4, 2:3,1000:230, 130:120,';

SELECT LeftItem, RightItem
  FROM dbo.SplitWithPairs(@ValuePairs, ',', ':')
  ORDER BY Position;
GO