sqlsql-serversql-server-2005t-sqluser-defined-functions

Split value pairs and a create table using UDF


I've been trying to write a Table-Valued function that takes value pairs as a parameter and return a table with two columns.

Below is the function signature I am trying to do.

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

I want to call the method like below

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

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

Can you see any nice way to split above ValuePairs sting and create a table with two columns?


Solution

  • Revisiting after a decade there are definitely better ways to do this today.

    SQL Server 2022, Azure SQL Database, Managed Instance

    (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)

    In SQL Server 2016 through 2019, a few changes:

    • STRING_SPLIT doesn't support ordinal until SQL Server 2022, so we can use OPENJSON instead
    • we need to use LTRIM/RTRIM since TRIM was added in SQL Server 2017
    • key is 0-based so we bump by 1 to get an equivalent 1-based position

    One note about this function is that you can't use , as the minor delimiter without changing the function, since , is how OPENJSON delimits. There are probably other characters you may want to stay from for both delimiters.

    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
      );
    

    Versions no longer supported

    And finally, the original answer, which will work on versions older than SQL Server 2016, I'll leave as it was, but with a disclaimer that it could be improved (no shortage of reading about there here and, generally, multi-statement table-valued functions - particularly with loops - are a big red flag):

    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