QUOTED_STRING_SPLIT()

QUOTED_STRING_SPLIT.sql

/*
    STRING_SPLIT(), but it takes quoted strings. Will probably not work with T-SQL quoting
    using square brackets, but regular apostrophes and double-quotes should be fine.


    Example usage:

    DECLARE @string     nvarchar(max)=N'Alpha;Bravo;"Charlie;Delta;Echo";Foxtrot;Golf;Hotel',
            @separator  nchar(1)=N';';

    SELECT *
    FROM dbo.QUOTED_STRING_SPLIT(@string, @separator, DEFAULT)


*/

CREATE OR ALTER FUNCTION dbo.QUOTED_STRING_SPLIT(
    @string     nvarchar(max),
    @separator  nchar(1),
    @quote      nchar(1)=N'"'
)
RETURNS TABLE
AS

    RETURN (
        --- STRING_AGG() together the quoted parts again
        SELECT STRING_AGG([value], @separator) WITHIN GROUP (ORDER BY ordinal) AS [value],
               _group+1 AS ordinal
        FROM (
            --- Compute group numbers, taking into account the quote character
            SELECT (CASE WHEN LEFT([value], 1)=@quote AND RIGHT([value], 1)=@quote THEN SUBSTRING([value], 2, LEN([value])-2)
                         WHEN LEFT([value], 1)=@quote THEN SUBSTRING([value], 2, LEN([value]))
                         WHEN RIGHT([value], 1)=@quote THEN LEFT([value], LEN([value])-1)
                         ELSE [value] END) AS [value],
                   ordinal,
                   SUM(ISNULL(1-_quoted, 0)) OVER (ORDER BY ordinal ROWS UNBOUNDED PRECEDING) AS _group
            FROM (
                --- Split the string by the defined delimiter
                SELECT [value],
                       ordinal,
                       --- Compute the group number: if the string starts with @quote, start a new block,
                       --- if the string ends with @quote, end the group.
                       SUM((CASE WHEN LEFT([value], 1)=@quote THEN 1 ELSE 0 END)+
                           (CASE WHEN RIGHT([value], 1)=@quote THEN -1 ELSE 0 END))
                                OVER (ORDER BY ordinal ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) AS _quoted
                FROM STRING_SPLIT(@string, @separator, 1)
                ) AS x
            ) AS x
        GROUP BY _group);

GO