Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
/*
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 juristiction 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