Parse URL or comma-delimited query string with SQL

parse-key-value-string.sql

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Adam DeHaven
-- Create date: 2019-10-25
-- Description:	Parses a key=value query string (delimited by a comma or ampersand) and return the key-value pairs as a table
-- Example 1: -- @input_string = 'key=value,key2=value2'; @delimiter = ',';
-- Example 2: -- @input_string = 'key=value&key2=value2'; @delimiter = "&";
-- =============================================
CREATE FUNCTION [dbo].[ParseKeyValueString]
(
    @input_string VARCHAR(MAX),
    @delimiter VARCHAR(1)
)
RETURNS @input_string_table TABLE (
    [key] VARCHAR(100), [value] VARCHAR(1000)
)
AS
BEGIN
    DECLARE @input_string_pair VARCHAR(2000);
    DECLARE @key VARCHAR(100);
    DECLARE @value VARCHAR(1000);

    WHILE LEN(@input_string) > 0
        BEGIN
            SET @input_string_pair = LEFT(@input_string, ISNULL(NULLIF(CHARINDEX(@delimiter, @input_string) - 1, -1), LEN(@input_string)));
            SET @input_string = SUBSTRING(@input_string, ISNULL(NULLIF(CHARINDEX(@delimiter, @input_string), 0), LEN(@input_string)) + 1, LEN(@input_string));

            SET @key = LEFT(@input_string_pair, ISNULL(NULLIF(CHARINDEX('=', @input_string_pair) - 1, -1), LEN(@input_string_pair)));
            SET @value = SUBSTRING( @input_string_pair, ISNULL(NULLIF(CHARINDEX('=', @input_string_pair), 0), LEN(@input_string_pair)) + 1, LEN(@input_string_pair));

            INSERT INTO @input_string_table
                ( [key], [value] )
            VALUES
                ( @key, @value )
        END

    RETURN

END
GO