dbo.Template_Split.sql

/*

Copyright Daniel Hutmacher under Creative Commons 4.0 license with attribution.
http://creativecommons.org/licenses/by/4.0/

Source: https://github.com/sqlsunday/sp_ctrl3

DISCLAIMER: This script may not be suitable to run in a production
            environment. I cannot assume any responsibility regarding
            the accuracy of the output information, performance
            impacts on your server, or any other consequence. If
            your jurisdiction does not allow for this kind of
            waiver/disclaimer, or if you do not accept these terms,
            you are NOT allowed to store, distribute or use this
            code in any way.

USAGE:      SELECT * FROM dbo.Template_Split('Wingtip Toys (Bethel Acres, OK)', '% (%, %)%')

RETURNS:    Similar to STRING_SPLIT(), this inline table-value function
            returns the components in a string, as defined by the "%" wildcard
            in the second parameter of the function.

VERSION:    2022-07-26

*/

CREATE OR ALTER FUNCTION dbo.Template_Split(
    @string         nvarchar(max),
    @pattern        nvarchar(100)
)
RETURNS TABLE
AS

    RETURN (
        --- First, let's turn this "%abc[def]ghi%" into "%abc_ghi%", so that we can
        --- compute the offsets generated by wildcard expressions like "[0-9]".
        WITH pat AS (
            SELECT @pattern AS _pattern

            UNION ALL

            SELECT CAST(STUFF(pat._pattern, x1._start_offset, x2._width, '_') AS nvarchar(100)) AS _pattern
            FROM pat
            OUTER APPLY (
                VALUES (NULLIF(CHARINDEX('[', _pattern), 0))
                ) AS x1(_start_offset)
            OUTER APPLY (
                VALUES (CHARINDEX(']', SUBSTRING(pat._pattern, x1._start_offset, LEN(pat._pattern))))
                ) AS x2(_width)
            WHERE pat._pattern LIKE '%[[]%]%'),

        --- Loop through each "%" in the pattern and its corresponding item in the string:
        cte AS (
            SELECT 0 AS position,
                   CAST(NULL AS nvarchar(max)) AS [value],
                   @string AS _string,
                   @pattern AS _pattern,
                   (SELECT _pattern FROM pat WHERE _pattern NOT LIKE '%[[]%]%') AS _plain_pattern

            UNION ALL

            SELECT position+1 AS position,
                   (CASE WHEN x1._next_pattern='' THEN ISNULL(LEFT(cte._string, NULLIF(PATINDEX(cte._pattern, cte._string), 0)-1), cte._string)
                         ELSE LEFT(cte._string, x1._offset-1) END) AS [value],
                   SUBSTRING(cte._string, x1._offset+x1._delimiter_width-1, LEN(cte._string)) AS _string,
                   x1._next_pattern AS _pattern,
                   x1._next_plain_pattern AS _plain_pattern
            FROM cte
            CROSS APPLY (
                VALUES (--- Offset in the pattern string to the next "%":
                        NULLIF(PATINDEX(LEFT(cte._pattern, CHARINDEX('%', SUBSTRING(cte._pattern, 2, 100))+1), cte._string), 0),
                        --- Same offset, but in the plaintext string:
                        CHARINDEX('%', SUBSTRING(cte._plain_pattern, 2, 100)+'%'),
                        --- What the pattern will look for the next iteration:
                        SUBSTRING(cte._pattern, CHARINDEX('%', SUBSTRING(cte._pattern, 2, 100)+'%')+1, 100),
                        --- Same, but for the "plaintext pattern":
                        SUBSTRING(cte._plain_pattern, CHARINDEX('%', SUBSTRING(cte._plain_pattern, 2, 100)+'%')+1, 100))
                ) AS x1(_offset, _delimiter_width, _next_pattern, _next_plain_pattern)
    
            WHERE cte._pattern IS NOT NULL
              AND x1._next_pattern!=''
               OR cte._string LIKE cte._pattern AND cte._string!='' AND cte._pattern!='')

        SELECT position, [value]
        FROM cte
        WHERE position>0
        );

GO