Parse URL

Parse URL String

Explore the snippet with some demo data here.

Description

URL strings are a very valuable source of information, but trying to get your regex exactly right is a pain.
This re-usable snippet can be applied to any URL to extract the exact part you’re after.
Helpfully, Snowflake has a PARSE_URL function that returns a JSON object of URL components.

SELECT KEY,VALUE 
FROM
   (
      SELECT parsed from
      (
         SELECT
            parse_url(url) as parsed
         FROM my_url
       )
   ),
LATERAL FLATTEN(input=> parsed)

where:

  • <url> is your URL string (e.g. "https://www.yoursite.com/pricing/details?myparam1=123&myparam2=abc#Ref1"

Usage

To use, just copy out the regex for whatever part of the URL you need. Or capture them all as in the example below:

WITH my_url as (select 'https://www.yoursite.com/pricing/details?myparam1=123&myparam2=abc#newsfeed' as url)

select KEY,VALUE FROM 
(
  SELECT parsed from 
    (
      select 
        parse_url(url) as parsed
      FROM my_url
    )
), 
LATERAL FLATTEN(input => parsed)
KEY VALUE
fragment “newsfeed”
host www.yoursite.com
parameters {“myparam1”:“123”,“myparam2”:“abc”}
port (Empty)
query “myparam1=123&myparam2=abc”
scheme “https”

References