sql server - TSQL: Nested split/parse of string into table (multiple concatenated Tag:Value in one string)

Question

I would like a TSQL table-values function that can perform nested parsing and return the result in a two column table.

Example:

select * from udf_nested_split('a-->1,b-->16,x-->99')

Would produce:

Tag|Value  
a|1  
b|16  
x|99  

Answer 1

This is a bit of a brute-force function, but it works... takes two parameters (one for the primary delimiter, one for the secondary delimiter):

IF EXISTS ( SELECT * from dbo.sysobjects WHERE id = object_id(N'[dbo].[doubleSplit]') 
            AND OBJECTPROPERTY(id, N'IsTableFunction') = 1 )
    DROP FUNCTION [dbo].[doubleSplit]
GO

CREATE FUNCTION dbo.doubleSplit ( 
        @sourceString varchar(MAX),
        @primaryDelimiter varchar(100),
        @secondaryDelimiter varchar(100) )
RETURNS @tblArray TABLE 
   (
    ElementID smallint IDENTITY(1,1),
    Element varchar(MAX),
    Value varchar(MAX)
   )
AS
BEGIN

    DECLARE @primaryIndex smallint
    DECLARE @secondaryIndex smallint
    DECLARE @primaryStart smallint
    DECLARE @secondaryStart smallint
    DECLARE @primaryDelimiterSize smallint
    DECLARE @seondaryDelimiterSize smallint
    DECLARE @primaryElement varchar(MAX);
    DECLARE @seondaryElement varchar(MAX);

    SET @primaryDelimiterSize = LEN(@primaryDelimiter)
    SET @seondaryDelimiterSize = LEN(@secondaryDelimiter)
    --loop through source string and add elements to destination table array
    WHILE LEN(@sourceString) > 0
    BEGIN
        SET @primaryIndex = CHARINDEX(@primaryDelimiter, @sourceString)
        IF @primaryIndex = 0
        BEGIN
            SET @secondaryIndex = CHARINDEX(@secondaryDelimiter, @sourceString)
            IF @secondaryIndex = 0
            BEGIN
                INSERT INTO @tblArray (Element, Value) VALUES(@sourceString, '')
            END
            ELSE
            BEGIN
                SET @secondaryStart = @secondaryIndex + @seondaryDelimiterSize;
                INSERT INTO @tblArray (Element, Value) 
                VALUES(SUBSTRING(@sourceString, 1, @secondaryIndex - 1), 
                       SUBSTRING(@sourceString, @secondaryIndex + @seondaryDelimiterSize, LEN(@sourceString) - @secondaryStart + 1))
            END
            BREAK
        END
        ELSE
        BEGIN
            SELECT @primaryElement = SUBSTRING(@sourceString, 1, @primaryIndex - 1);
            SET @secondaryIndex = CHARINDEX(@secondaryDelimiter, @primaryElement)
            SET @secondaryStart = @secondaryIndex + @seondaryDelimiterSize;
            INSERT INTO @tblArray (Element, Value) 
            VALUES(SUBSTRING(@primaryElement, 1, @secondaryIndex - 1), 
                   SUBSTRING(@primaryElement, @secondaryIndex + @seondaryDelimiterSize, LEN(@primaryElement) - @secondaryStart + 1))
            SET @primaryStart = @primaryIndex + @primaryDelimiterSize
            SET @sourceString = SUBSTRING(@sourceString, @primaryStart , LEN(@sourceString) - @primaryStart + 1)
        END
    END

    RETURN
END
GO

You can then call it like this and get the expected results:

SELECT * FROM dbo.doubleSplit('a-->1,b-->16,x-->99', ',', '-->')

Returns this:

ElementID   Element Value
1           a       1
2           b       16
3           x       99

And this:

SELECT * FROM dbo.doubleSplit('a-->1', ',', '-->')

Returns this:

ElementID  Element  Value
1          a        1

Etc., etc.