URL
URL functions reference.
cutFragment
Removes the fragment identifier, including the number sign, from a URL.
Syntax
cutFragment(url)Arguments
url— URL.String
Returned value
Returns the URL with fragment identifier removed. String
Examples
Usage example
SELECT cutFragment('http://example.com/path?query=value#fragment123');┌─cutFragment('http://example.com/path?query=value#fragment123')─┐
│ http://example.com/path?query=value │
└────────────────────────────────────────────────────────────────┘Introduced in version 1.1.
cutQueryString
Removes the query string, including the question mark from a URL.
Syntax
cutQueryString(url)Arguments
url— URL.String
Returned value
Returns the URL with query string removed. String
Examples
Usage example
SELECT cutQueryString('http://example.com/path?query=value¶m=123#fragment');┌─cutQueryString('http://example.com/path?query=value¶m=123#fragment')─┐
│ http://example.com/path#fragment │
└──────────────────────────────────────────────────────────────────────────┘Introduced in version 1.1.
cutQueryStringAndFragment
Removes the query string and fragment identifier, including the question mark and number sign, from a URL.
Syntax
cutQueryStringAndFragment(url)Arguments
url— URL.String
Returned value
Returns the URL with query string and fragment identifier removed. String
Examples
Usage example
SELECT cutQueryStringAndFragment('http://example.com/path?query=value¶m=123#fragment');┌─cutQueryStringAndFragment('http://example.com/path?query=value¶m=123#fragment')─┐
│ http://example.com/path │
└─────────────────────────────────────────────────────────────────────────────────────┘Introduced in version 1.1.
cutToFirstSignificantSubdomain
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain.
Syntax
cutToFirstSignificantSubdomain(url)Arguments
url— URL or domain string to process.String
Returned value
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain if possible, otherwise returns an empty string. String
Examples
Usage example
SELECT
cutToFirstSignificantSubdomain('https://news.rawtree.com.tr/'),
cutToFirstSignificantSubdomain('www.tr'),
cutToFirstSignificantSubdomain('tr');┌─cutToFirstSignificantSubdomain('https://news.rawtree.com.tr/')─┬─cutToFirstSignificantSubdomain('www.tr')─┬─cutToFirstSignificantSubdomain('tr')─┐
│ rawtree.com.tr │ tr │ │
└───────────────────────────────────────────────────────────────────┴──────────────────────────────────────────┴──────────────────────────────────────┘Introduced in version 1.1.
cutToFirstSignificantSubdomainCustom
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain. Accepts custom TLD list name. This function can be useful if you need a fresh TLD list or if you have a custom list.
Configuration example
<top_level_domains_lists>
<public_suffix_list>public_suffix_list.dat</public_suffix_list>
</top_level_domains_lists>Syntax
cutToFirstSignificantSubdomainCustom(url, tld_list_name)Arguments
url— URL or domain string to process.Stringtld_list_name— Name of the custom TLD list configured in RawTree.const String
Returned value
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain. String
Examples
Using custom TLD list for non-standard domains
SELECT cutToFirstSignificantSubdomainCustom('bar.foo.there-is-no-such-domain', 'public_suffix_list')foo.there-is-no-such-domainIntroduced in version 21.1.
cutToFirstSignificantSubdomainCustomRFC
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain. Accepts custom TLD list name. This function can be useful if you need a fresh TLD list or if you have a custom list. Similar to cutToFirstSignificantSubdomainCustom but conforms to RFC 3986.
Configuration example
<top_level_domains_lists>
<public_suffix_list>public_suffix_list.dat</public_suffix_list>
</top_level_domains_lists>Syntax
cutToFirstSignificantSubdomainCustomRFC(url, tld_list_name)Arguments
url— URL or domain string to process according to RFC 3986. -tld_list_name— Name of the custom TLD list configured in RawTree.
Returned value
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain. String
Examples
Usage example
SELECT cutToFirstSignificantSubdomainCustomRFC('www.foo', 'public_suffix_list');┌─cutToFirstSignificantSubdomainCustomRFC('www.foo', 'public_suffix_list')─────┐
│ www.foo │
└──────────────────────────────────────────────────────────────────────────────┘Introduced in version 22.10.
cutToFirstSignificantSubdomainCustomWithWWW
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain without stripping 'www'. Accepts custom TLD list name. It can be useful if you need a fresh TLD list or if you have a custom list.
Configuration example
<top_level_domains_lists>
<public_suffix_list>public_suffix_list.dat</public_suffix_list>
</top_level_domains_lists>
**Syntax**
```sql
cutToFirstSignificantSubdomainCustomWithWWW(url, tld_list_name)Arguments
url— URL or domain string to process. -tld_list_name— Name of the custom TLD list configured in RawTree.
Returned value
Part of the domain that includes top-level subdomains up to the first significant subdomain without stripping 'www'. String
Examples
Usage example
SELECT cutToFirstSignificantSubdomainCustomWithWWW('www.foo', 'public_suffix_list');┌─cutToFirstSignificantSubdomainCustomWithWWW('www.foo', 'public_suffix_list')─┐
│ www.foo │
└──────────────────────────────────────────────────────────────────────────────┘Introduced in version 21.1.
cutToFirstSignificantSubdomainCustomWithWWWRFC
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain without stripping www.
Accepts custom TLD list name.
It can be useful if you need a fresh TLD list or if you have a custom list.
Similar to cutToFirstSignificantSubdomainCustomWithWWW but conforms to RFC 3986.
Configuration example
<top_level_domains_lists>
<public_suffix_list>public_suffix_list.dat</public_suffix_list>
</top_level_domains_lists>
**Syntax**
```sql
cutToFirstSignificantSubdomainCustomWithWWWRFC(url, tld_list_name)Arguments
url— URL or domain string to process according to RFC 3986. -tld_list_name— Name of the custom TLD list configured in RawTree.
Returned value
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain without stripping www. String
Examples
RFC 3986 parsing preserving www with custom TLD list
SELECT cutToFirstSignificantSubdomainCustomWithWWWRFC('https://www.subdomain.example.custom', 'public_suffix_list')www.example.customIntroduced in version 22.10.
cutToFirstSignificantSubdomainRFC
Returns the part of the domain that includes top-level subdomains up to the "first significant subdomain". Similar to cutToFirstSignificantSubdomain but conforms to RFC 3986.
Syntax
cutToFirstSignificantSubdomainRFC(url)Arguments
url— URL or domain string to process according to RFC 3986.String
Returned value
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain if possible, otherwise returns an empty string. String
Examples
Usage example
SELECT
cutToFirstSignificantSubdomain('http://user:password@example.com:8080'),
cutToFirstSignificantSubdomainRFC('http://user:password@example.com:8080');┌─cutToFirstSignificantSubdomain('http://user:password@example.com:8080')─┬─cutToFirstSignificantSubdomainRFC('http://user:password@example.com:8080')─┐
│ │ example.com │
└─────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────┘Introduced in version 22.10.
cutToFirstSignificantSubdomainWithWWW
Returns the part of the domain that includes top-level subdomains up to the "first significant subdomain", without stripping 'www.'.
Similar to cutToFirstSignificantSubdomain but preserves the 'www.' prefix if present.
Syntax
cutToFirstSignificantSubdomainWithWWW(url)Arguments
url— URL or domain string to process.String
Returned value
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain (with www) if possible, otherwise returns an empty string. String
Examples
Usage example
SELECT
cutToFirstSignificantSubdomainWithWWW('https://news.rawtree.com.tr/'),
cutToFirstSignificantSubdomainWithWWW('www.tr'),
cutToFirstSignificantSubdomainWithWWW('tr');┌─cutToFirstSignificantSubdomainWithWWW('https://news.rawtree.com.tr/')─┬─cutToFirstSignificantSubdomainWithWWW('www.tr')─┬─cutToFirstSignificantSubdomainWithWWW('tr')─┐
│ rawtree.com.tr │ www.tr │ │
└──────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────────┴─────────────────────────────────────────────┘Introduced in version 20.12.
cutToFirstSignificantSubdomainWithWWWRFC
Returns the part of the domain that includes top-level subdomains up to the "first significant subdomain", without stripping 'www'. Similar to cutToFirstSignificantSubdomainWithWWW but conforms to RFC 3986.
Syntax
cutToFirstSignificantSubdomainWithWWWRFC(url)Arguments
url— URL or domain string to process according to RFC 3986.
Returned value
Returns the part of the domain that includes top-level subdomains up to the first significant subdomain (with 'www') if possible, otherwise returns an empty string String
Examples
Usage example
SELECT
cutToFirstSignificantSubdomainWithWWW('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy'),
cutToFirstSignificantSubdomainWithWWWRFC('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy');┌─cutToFirstSignificantSubdomainWithWWW('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy')─┬─cutToFirstSignificantSubdomainWithWWWRFC('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy')─┐
│ │ mail.ru │
└───────────────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────┘Introduced in version 22.10.
cutURLParameter
Removes the name parameter from a URL, if present.
This function does not encode or decode characters in parameter names, e.g. Client ID and Client%20ID are treated as different parameter names.
Syntax
cutURLParameter(url, name)Arguments
url— URL.Stringname— Name of URL parameter.StringorArray(String)
Returned value
URL with name URL parameter removed. String
Examples
Usage example
SELECT
cutURLParameter('http://bigmir.net/?a=b&c=d&e=f#g', 'a') AS url_without_a,
cutURLParameter('http://bigmir.net/?a=b&c=d&e=f#g', ['c', 'e']) AS url_without_c_and_e;┌─url_without_a────────────────┬─url_without_c_and_e──────┐
│ http://bigmir.net/?c=d&e=f#g │ http://bigmir.net/?a=b#g │
└──────────────────────────────┴──────────────────────────┘Introduced in version 1.1.
cutWWW
Removes the leading www., if present, from the URL's domain.
Syntax
cutWWW(url)Arguments
url— URL.String
Returned value
Returns the URL with leading www. removed from the domain. String
Examples
Usage example
SELECT cutWWW('http://www.example.com/path?query=value#fragment');┌─cutWWW('http://www.example.com/path?query=value#fragment')─┐
│ http://example.com/path?query=value#fragment │
└────────────────────────────────────────────────────────────┘Introduced in version 1.1.
decodeURLComponent
Takes a URL-encoded string as input and decodes it back to its original, readable form.
Syntax
decodeURLComponent(url)Arguments
url— URL.String
Returned value
Returns the decoded URL. String
Examples
Usage example
SELECT decodeURLComponent('http://127.0.0.1:8123/?query=SELECT%201%3B') AS DecodedURL;┌─DecodedURL─────────────────────────────┐
│ http://127.0.0.1:8123/?query=SELECT 1; │
└────────────────────────────────────────┘Introduced in version 1.1.
decodeURLFormComponent
Decodes URL-encoded strings using form encoding rules (RFC-1866), where + signs are converted to spaces and percent-encoded characters are decoded.
Syntax
decodeURLFormComponent(url)Arguments
url— URL.String
Returned value
Returns the decoded URL. String
Examples
Usage example
SELECT decodeURLFormComponent('http://127.0.0.1:8123/?query=SELECT%201+2%2B3') AS DecodedURL;┌─DecodedURL────────────────────────────────┐
│ http://127.0.0.1:8123/?query=SELECT 1 2+3 │
└───────────────────────────────────────────┘Introduced in version 1.1.
domain
Extracts the hostname from a URL.
The URL can be specified with or without a protocol.
Syntax
domain(url)Arguments
url— URL.String
Returned value
Returns the host name if the input string can be parsed as a URL, otherwise an empty string. String
Examples
Usage example
SELECT domain('svn+ssh://some.svn-hosting.com:80/repo/trunk');┌─domain('svn+ssh://some.svn-hosting.com:80/repo/trunk')─┐
│ some.svn-hosting.com │
└────────────────────────────────────────────────────────┘Introduced in version 1.1.
domainRFC
Extracts the hostname from a URL.
Similar to domain, but RFC 3986 conformant.
Syntax
domainRFC(url)Arguments
url— URL.String
Returned value
Returns the host name if the input string can be parsed as a URL, otherwise an empty string. String
Examples
Usage example
SELECT
domain('http://user:password@example.com:8080/path?query=value#fragment'),
domainRFC('http://user:password@example.com:8080/path?query=value#fragment');┌─domain('http://user:password@example.com:8080/path?query=value#fragment')─┬─domainRFC('http://user:password@example.com:8080/path?query=value#fragment')─┐
│ │ example.com │
└───────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────┘Introduced in version 22.10.
domainWithoutWWW
Returns the domain of a URL without leading www. if present.
Syntax
domainWithoutWWW(url)Arguments
url— URL.String
Returned value
Returns the domain name if the input string can be parsed as a URL (without leading www.), otherwise an empty string. String
Examples
Usage example
SELECT domainWithoutWWW('http://paul@www.example.com:80/');┌─domainWithoutWWW('http://paul@www.example.com:80/')─┐
│ example.com │
└─────────────────────────────────────────────────────┘Introduced in version 1.1.
domainWithoutWWWRFC
Returns the domain without leading www. if present. Similar to domainWithoutWWW but conforms to RFC 3986.
Syntax
domainWithoutWWWRFC(url)Arguments
url— URL.String
Returned value
Returns the domain name if the input string can be parsed as a URL (without leading www.), otherwise an empty string. String
Examples
Usage example
SELECT
domainWithoutWWW('http://user:password@www.example.com:8080/path?query=value#fragment'),
domainWithoutWWWRFC('http://user:password@www.example.com:8080/path?query=value#fragment');┌─domainWithoutWWW('http://user:password@www.example.com:8080/path?query=value#fragment')─┬─domainWithoutWWWRFC('http://user:password@www.example.com:8080/path?query=value#fragment')─┐
│ │ example.com │
└─────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────┘Introduced in version 1.1.
encodeURLComponent
Takes a regular string and converts it into a URL-encoded (percent-encoded) format where special characters are replaced with their percent-encoded equivalents.
Syntax
encodeURLComponent(url)Arguments
url— URL.String
Returned value
Returns the encoded URL. String
Examples
Usage example
SELECT encodeURLComponent('http://127.0.0.1:8123/?query=SELECT 1;') AS EncodedURL;┌─EncodedURL───────────────────────────────────────────────┐
│ http%3A%2F%2F127.0.0.1%3A8123%2F%3Fquery%3DSELECT%201%3B │
└──────────────────────────────────────────────────────────┘Introduced in version 22.3.
encodeURLFormComponent
Encodes strings using form encoding rules (RFC-1866), where spaces are converted to + signs and special characters are percent-encoded.
Syntax
encodeURLFormComponent(url)Arguments
url— URL.String
Returned value
Returns the encoded URL. String
Examples
Usage example
SELECT encodeURLFormComponent('http://127.0.0.1:8123/?query=SELECT 1 2+3') AS EncodedURL;┌─EncodedURL────────────────────────────────────────────────┐
│ http%3A%2F%2F127.0.0.1%3A8123%2F%3Fquery%3DSELECT+1+2%2B3 │
└───────────────────────────────────────────────────────────┘Introduced in version 22.3.
extractURLParameter
Returns the value of the name parameter in the URL, if present, otherwise an empty string is returned.
If there are multiple parameters with this name, the first occurrence is returned.
The function assumes that the parameter in the url parameter is encoded in the same way as in the name argument.
Syntax
extractURLParameter(url, name)Arguments
Returned value
Returns the value of the URL parameter with the specified name. String
Examples
Usage example
SELECT extractURLParameter('http://example.com/?param1=value1¶m2=value2', 'param1');┌─extractURLPa⋯, 'param1')─┐
│ value1 │
└──────────────────────────┘Introduced in version 1.1.
extractURLParameterNames
Returns an array of name strings corresponding to the names of URL parameters. The values are not decoded.
Syntax
extractURLParameterNames(url)Arguments
url— URL.String
Returned value
Returns an array of name strings corresponding to the names of URL parameters. Array(String)
Examples
Usage example
SELECT extractURLParameterNames('http://example.com/?param1=value1¶m2=value2');┌─extractURLPa⋯m2=value2')─┐
│ ['param1','param2'] │
└──────────────────────────┘Introduced in version 1.1.
extractURLParameters
Returns an array of name=value strings corresponding to the URL parameters.
The values are not decoded.
Syntax
extractURLParameters(url)Arguments
url— URL.String
Returned value
Returns an array of name=value strings corresponding to the URL parameters. Array(String)
Examples
Usage example
SELECT extractURLParameters('http://example.com/?param1=value1¶m2=value2');┌─extractURLParame⋯¶m2=value2')─┐
│ ['param1=value1','param2=value2'] │
└───────────────────────────────────┘Introduced in version 1.1.
firstSignificantSubdomain
Returns the "first significant subdomain".
The first significant subdomain is a second-level domain if it is 'com', 'net', 'org', or 'co'. Otherwise, it is a third-level domain.
For example, firstSignificantSubdomain('https://news.rawtree.com/') = 'rawtree', firstSignificantSubdomain ('https://news.rawtree.com.tr/') = 'rawtree'.
The list of "insignificant" second-level domains and other implementation details may change in the future.
Examples
firstSignificantSubdomain
SELECT firstSignificantSubdomain('https://news.rawtree.com/')firstSignificantSubdomainRFC
Returns the "first significant subdomain" according to RFC 1034.
fragment
Returns the fragment identifier without the initial hash symbol.
Syntax
fragment(url)Arguments
url— URL.String
Returned value
Returns the fragment identifier without the initial hash symbol. String
Examples
Usage example
SELECT fragment('https://rawtree.com/docs/getting-started/quick-start/cloud#1-create-a-rawtree-service');┌─fragment('http⋯ouse-service')─┐
│ 1-create-a-rawtree-service │
└───────────────────────────────┘Introduced in version 1.1.
netloc
Extracts network locality (username:password@host:port) from a URL.
Syntax
netloc(url)Arguments
url— URL.String
Returned value
Returns username:password@host:port from a given URL. String
Examples
Usage example
SELECT netloc('http://paul@www.example.com:80/');┌─netloc('http⋯e.com:80/')─┐
│ paul@www.example.com:80 │
└──────────────────────────┘Introduced in version 20.5.
path
Returns the path without query string from a URL.
Syntax
path(url)Arguments
url— URL.String
Returned value
Returns the path of the URL without query string. String
Examples
Usage example
SELECT path('https://rawtree.com/docs/sql-reference/functions/url-functions/?query=value');┌─path('https://rawtree.com/en/sql-reference/functions/url-functions/?query=value')─┐
│ /docs/sql-reference/functions/url-functions/ │
└──────────────────────────────────────────────────────────────────────────────────────┘Introduced in version 1.1.
pathFull
The same as path, but includes the query string and fragment of the URL.
Syntax
pathFull(url)Arguments
url— URL.String
Returned value
Returns the path of the URL including query string and fragment. String
Examples
Usage example
SELECT pathFull('https://rawtree.com/docs/sql-reference/functions/url-functions/?query=value#section');┌─pathFull('https://rawtree.com⋯unctions/?query=value#section')─┐
│ /docs/sql-reference/functions/url-functions/?query=value#section │
└──────────────────────────────────────────────────────────────────┘Introduced in version 1.1.
port
Returns the port of a URL, or the default_port if the URL contains no port or cannot be parsed.
Syntax
port(url[, default_port])Arguments
url— URL.Stringdefault_port— Optional. The default port number to be returned.0by default.UInt16
Returned value
Returns the port of the URL, or the default port if there is no port in the URL or in case of a validation error. UInt16
Examples
Usage example
SELECT port('https://rawtree.com:8443/docs'), port('https://rawtree.com/docs', 443);┌─port('https://rawtree.com:8443/docs')─┬─port('https://rawtree.com/docs', 443)─┐
│ 8443 │ 443 │
└──────────────────────────────────────────┴──────────────────────────────────────────┘Introduced in version 20.5.
portRFC
Returns the port or default_port if the URL contains no port or cannot be parsed.
Similar to port, but RFC 3986 conformant.
Syntax
portRFC(url[, default_port])Arguments
url— URL.Stringdefault_port— Optional. The default port number to be returned.0by default.UInt16
Returned value
Returns the port or the default port if there is no port in the URL or in case of a validation error. UInt16
Examples
Usage example
SELECT port('http://user:password@example.com:8080/'), portRFC('http://user:password@example.com:8080/');┌─port('http:/⋯com:8080/')─┬─portRFC('htt⋯com:8080/')─┐
│ 0 │ 8080 │
└──────────────────────────┴──────────────────────────┘Introduced in version 22.10.
protocol
Extracts the protocol from a URL.
Examples of typical returned values: http, https, ftp, mailto, tel, magnet.
Syntax
protocol(url)Arguments
url— URL.String
Returned value
Returns the protocol of the URL, or an empty string if it cannot be determined. String
Examples
Usage example
SELECT protocol('https://rawtree.com/');┌─protocol('https://rawtree.com/')─┐
│ https │
└─────────────────────────────────────┘Introduced in version 1.1.
queryString
Returns the query string of a URL without the initial question mark, # and everything after #.
Syntax
queryString(url)Arguments
url— URL.String
Returned value
Returns the query string of the URL without the initial question mark and fragment. String
Examples
Usage example
SELECT queryString('https://rawtree.com/docs?query=value¶m=123#section');┌─queryString(⋯3#section')─┐
│ query=value¶m=123 │
└──────────────────────────┘Introduced in version 1.1.
queryStringAndFragment
Returns the query string and fragment identifier of a URL.
Syntax
queryStringAndFragment(url)Arguments
url— URL.String
Returned value
Returns the query string and fragment identifier of the URL. String
Examples
Usage example
SELECT queryStringAndFragment('https://rawtree.com/docs?query=value¶m=123#section');┌─queryStringAnd⋯=123#section')─┐
│ query=value¶m=123#section │
└───────────────────────────────┘Introduced in version 1.1.
topLevelDomain
Extracts the the top-level domain from a URL.
:::note The URL can be specified with or without a protocol. For example:
svn+ssh://some.svn-hosting.com:80/repo/trunk
some.svn-hosting.com:80/repo/trunk
https://rawtree.com/time/:::
Syntax
topLevelDomain(url)Arguments
url— URL.String
Returned value
Returns the domain name if the input string can be parsed as a URL. Otherwise, an empty string. String
Examples
Usage example
SELECT topLevelDomain('svn+ssh://www.some.svn-hosting.com:80/repo/trunk');┌─topLevelDomain('svn+ssh://www.some.svn-hosting.com:80/repo/trunk')─┐
│ com │
└────────────────────────────────────────────────────────────────────┘Introduced in version 1.1.
topLevelDomainRFC
Extracts the the top-level domain from a URL.
Similar to topLevelDomain, but conforms to RFC 3986.
Syntax
topLevelDomainRFC(url)Arguments
url— URL.String
Returned value
Domain name if the input string can be parsed as a URL. Otherwise, an empty string. String
Examples
Usage example
SELECT topLevelDomain('http://foo:foo%41bar@foo.com'), topLevelDomainRFC('http://foo:foo%41bar@foo.com');┌─topLevelDomain('http://foo:foo%41bar@foo.com')─┬─topLevelDomainRFC('http://foo:foo%41bar@foo.com')─┐
│ │ com │
└────────────────────────────────────────────────┴───────────────────────────────────────────────────┘Introduced in version 22.10.