Skip to content

Instantly share code, notes, and snippets.

@dhmacher

dhmacher/dbo.Template_Split.sql

Last active September 13, 2022 07:05
    Show Gist options
    •
    • Save dhmacher/42d4e44bf14a0b2f52fcb511b5657de8 to your computer and use it in GitHub Desktop.
    Save dhmacher/42d4e44bf14a0b2f52fcb511b5657de8 to your computer and use it in GitHub Desktop.
    Download ZIP
    Raw
    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
    Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment