Thursday, April 9, 2020

Parse URL in SQL

I searched a lot, and I did not like anything I found. Then I saw this on sql-server-helper.com That site looks a little odd, I thought I would save this for myself, in case the site goes away, like some I used before stackoverflow.
CREATE FUNCTION [dbo].[ParseURLQueryString]
( @QueryString AS VARCHAR(MAX) )
RETURNS @QueryStringTable TABLE
( [Key] VARCHAR(100), [Value] VARCHAR(1000) )
AS
BEGIN
DECLARE @QueryStringPair VARCHAR(2000)
DECLARE @Key VARCHAR(100)
DECLARE @Value VARCHAR(1000)

WHILE LEN(@QueryString) > 0
BEGIN
SET @QueryStringPair = LEFT ( @QueryString, ISNULL(NULLIF(CHARINDEX('&', @QueryString) - 1, -1),
LEN(@QueryString)))
SET @QueryString = SUBSTRING( @QueryString, ISNULL(NULLIF(CHARINDEX('&', @QueryString), 0),
LEN(@QueryString)) + 1, LEN(@QueryString))

SET @Key = LEFT (@QueryStringPair, ISNULL(NULLIF(CHARINDEX('=', @QueryStringPair) - 1, -1),
LEN(@QueryStringPair)))
SET @Value = SUBSTRING( @QueryStringPair, ISNULL(NULLIF(CHARINDEX('=', @QueryStringPair), 0),
LEN(@QueryStringPair)) + 1, LEN(@QueryStringPair))

INSERT INTO @QueryStringTable ( [Key], [Value] )
VALUES ( @Key, @Value )
END

RETURN
END

And this is how you use it
SELECT * FROM [dbo].[ParseURLQueryString] ( 'fname=Barack&lname=Obama&addr=1600 Pennsylvania Ave NW&city=Washington&st=DC&zip=20500' )

No comments: