URL functions

PARSE_URL

PARSE_URL(url, part [, key]) → varchar

Extracts the component specified by part from url. All parameters are of type STRING, and the function returns STRING.

The part value is case-insensitive. Supported values:

When part is QUERY, pass key to retrieve the value of a specific query parameter.

Returns NULL if url, part, or key is NULL, or if part is an invalid value.

Examples

All examples use the same URL: file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose

-- Extract the host
SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'HOST');
-- example.com

-- Extract the path
SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'PATH');
-- /over/there/index.dtb

-- Extract the value of the "type" query parameter
SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'QUERY', 'type');
-- animal

-- Extract the fragment identifier
SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'REF');
-- nose

-- Extract the protocol
SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'PROTOCOL');
-- file

-- Extract the authority (user info + hostname + port)
SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'AUTHORITY');
-- username:password@example.com:8042

-- Extract the user information
SELECT parse_url('file://username:password@example.com:8042/over/there/index.dtb?type=animal&name=narwhal#nose', 'USERINFO');
-- username:password

URL_DECODE

URL_DECODE(input) → varchar

Decodes a string encoded in application/x-www-form-urlencoded format. This function is the inverse of URL_ENCODE.

Returns NULL if input is NULL.

Decoding rules

• Characters a–z, A–Z, and 0–9 are unchanged.

• ., -, *, and _ are unchanged.

• + is converted to a space.

• Percent-encoded sequences (%xy) are decoded into their corresponding byte values, then interpreted as a UTF-8 string.

Example

SELECT url_decode('example+for+url_decode%3A%2F%2F+%28fdsf%29');
-- example for url_decode:// (fdsf)

URL_ENCODE

URL_ENCODE(input) → varchar

Encodes a string using the application/x-www-form-urlencoded format. This function is the inverse of URL_DECODE.

Returns NULL if input is NULL.

Encoding rules

• Characters a–z, A–Z, and 0–9 are unchanged.

• ., -, *, and _ are unchanged.

• Spaces are converted to +.

• All other characters are encoded as UTF-8 bytes in %xy format, where xy is the hexadecimal byte value.

Example

SELECT url_encode('example for url_encode:// (fdsf)');
-- example+for+url_encode%3A%2F%2F+%28fdsf%29

URL extraction functions

The following functions extract individual components from a URL. They accept URLs conforming to the standard structure:

[protocol:][//host[:port]][path][?query][#fragment]

The extracted value does not include URI syntax separators such as :, ?, or #. All functions accept a STRING argument and return STRING.

All functions return NULL if url is NULL or invalid.

URL_EXTRACT_FRAGMENT(url) → varchar

SELECT url_extract_fragment('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');
-- Ref1

URL_EXTRACT_HOST(url) → varchar

SELECT url_extract_host('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');
-- example.com

URL_EXTRACT_PARAMETER(url, parameter) → varchar

-- "k1" exists in the query string
SELECT url_extract_parameter('http://example.com/path1/p.php?k1=v1&k2=v2&k3&k4#Ref1', 'k1');
-- v1

-- "k5" does not exist in the query string
SELECT url_extract_parameter('http://example.com/path1/p.php?k1=v1&k2=v2&k3&k4#Ref1', 'k5');
-- NULL

URL_EXTRACT_PATH(url) → varchar

SELECT url_extract_path('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');
-- /path1/p.php

URL_EXTRACT_PORT(url) → varchar

-- No port specified
SELECT url_extract_port('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');
-- NULL

-- Port is specified
SELECT url_extract_port('http://example.com:8080/path1/p.php?k1=v1&k2=v2#Ref1');
-- 8080

URL_EXTRACT_PROTOCOL(url) → varchar

SELECT url_extract_protocol('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');
-- http

URL_EXTRACT_QUERY(url) → varchar

SELECT url_extract_query('http://example.com/path1/p.php?k1=v1&k2=v2#Ref1');
-- k1=v1&k2=v2