DEV Community

Dendi Handian
Dendi Handian

Posted on

URL Functions in Presto/Athena

#sql #database #presto #athena

Extract Host 

SELECT url_extract_host('https://example-subdomain.example-host.com/path/to/detail?order=desc&page=10')

-- example-subdomain.example-host.com
Enter fullscreen mode Exit fullscreen mode

Extract Parameter 

SELECT url_extract_parameter('https://example-subdomain.example-host.com/path/to/detail?order=desc&page=10', 'page')

-- 10
Enter fullscreen mode Exit fullscreen mode

Extract Path 

SELECT url_extract_path('https://example-subdomain.example-host.com/path/to/detail?order=desc&page=10')

-- /path/to/detail
Enter fullscreen mode Exit fullscreen mode

Extract Port 

SELECT url_extract_port('https://example-subdomain.example-host.com:8088/path/to/detail?order=desc&page=10')

-- 8088
Enter fullscreen mode Exit fullscreen mode

Extract Protocol 

SELECT url_extract_protocol('https://example-subdomain.example-host.com:8088/path/to/detail?order=desc&page=10')

-- https
Enter fullscreen mode Exit fullscreen mode

Extract Query 

SELECT url_extract_query('https://example-subdomain.example-host.com:8088/path/to/detail?order=desc&page=10')

-- order=desc&page=10
Enter fullscreen mode Exit fullscreen mode

Extract Fragment 

SELECT url_extract_fragment('https://example-subdomain.example-host.com:8088/path/to/detail?order=desc&page=10#header1')

-- header1
Enter fullscreen mode Exit fullscreen mode

Top comments (2)

Collapse
 
dorepov profile image
xavyfow •

When this will be useful and where we can use this codes @dendihandian

Collapse
 
dendihandian profile image
Dendi Handian • • Edited

If you have AWS account, you can try it on Athena. I'm still looking for presto sql playground to be tried for these queries...