ReferenceSQL ReferenceFunctions

URL

URL functions reference.

cutFragment

Removes the fragment identifier, including the number sign, from a URL.

Syntax

cutFragment(url)

Arguments

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

Returned value

Returns the URL with query string removed. String

Examples

Usage example

SELECT cutQueryString('http://example.com/path?query=value&param=123#fragment');
┌─cutQueryString('http://example.com/path?query=value&param=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

Returned value

Returns the URL with query string and fragment identifier removed. String

Examples

Usage example

SELECT cutQueryStringAndFragment('http://example.com/path?query=value&param=123#fragment');
┌─cutQueryStringAndFragment('http://example.com/path?query=value&param=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. String
  • tld_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-domain

Introduced 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.custom

Introduced 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

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

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

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

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

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

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

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

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

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

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&param2=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

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&param2=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

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&param2=value2');
┌─extractURLParame⋯&param2=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

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

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

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

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. String
  • default_port — Optional. The default port number to be returned. 0 by 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. String
  • default_port — Optional. The default port number to be returned. 0 by 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

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

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&param=123#section');
┌─queryString(⋯3#section')─┐
│ query=value&param=123    │
└──────────────────────────┘

Introduced in version 1.1.

queryStringAndFragment

Returns the query string and fragment identifier of a URL.

Syntax

queryStringAndFragment(url)

Arguments

Returned value

Returns the query string and fragment identifier of the URL. String

Examples

Usage example

SELECT queryStringAndFragment('https://rawtree.com/docs?query=value&param=123#section');
┌─queryStringAnd⋯=123#section')─┐
│ query=value&param=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

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

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.