Skip to main content
Skip to main content
Edit this page

Functions for Working with URLs

Note

The functions mentioned in this section are optimized for maximum performance and for the most part do not follow the RFC-3986 standard. Functions which implement RFC-3986 have RFC appended to their function name and are generally slower.

You can generally use the non-RFC function variants when working with publicly registered domains that contain neither user strings nor @ symbols. The table below details which symbols in a URL can (✔) or cannot (✗) be parsed by the respective RFC and non-RFC variants:

Symbolnon-RFCRFC
' '✗✗
\t✗✗
<✗✗
>✗✗
%✗✔*
{✗✗
}✗✗
|✗✗
\\✗✗
^✗✗
~✗✔*
[✗✗
]✗✔
;✗✔*
=✗✔*
&✗✔*

symbols marked * are sub-delimiters in RFC 3986 and allowed for user info following the @ symbol.

Functions that extract parts of a URL​

If the relevant part isn't present in a URL, an empty string is returned.

protocol​

Extracts the protocol from a URL.

Examples of typical returned values: http, https, ftp, mailto, tel, magnet.

domain​

Extracts the hostname from a URL.

Syntax

domain(url)

Arguments

The URL can be specified with or without a protocol. Examples:

svn+ssh://some.svn-hosting.com:80/repo/trunk
some.svn-hosting.com:80/repo/trunk
https://clickhouse.com/time/

For these examples, the domain function returns the following results:

some.svn-hosting.com
some.svn-hosting.com
clickhouse.com

Returned values

  • Host name if the input string can be parsed as a URL, otherwise an empty string. String.

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                                   │
└────────────────────────────────────────────────────────┘

domainRFC​

Extracts the hostname from a URL. Similar to domain, but RFC 3986 conformant.

Syntax

domainRFC(url)

Arguments

Returned values

  • Host name if the input string can be parsed as a URL, otherwise an empty string. String.

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                                                                  │
└───────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────┘

domainWithoutWWW​

Returns the domain without leading www. if present.

Syntax

domainWithoutWWW(url)

Arguments

Returned values

  • Domain name if the input string can be parsed as a URL (without leading www.), otherwise an empty string. String.

Example

SELECT domainWithoutWWW('http://paul@www.example.com:80/');

┌─domainWithoutWWW('http://paul@www.example.com:80/')─┐
│ example.com                                         │
└─────────────────────────────────────────────────────┘

domainWithoutWWWRFC​

Returns the domain without leading www. if present. Similar to domainWithoutWWW but conforms to RFC 3986.

Syntax

domainWithoutWWWRFC(url)

Arguments

Returned values

  • Domain name if the input string can be parsed as a URL (without leading www.), otherwise an empty string. String.

Example

Query:

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');

Result:

┌─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                                                                                │
└─────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────┘

topLevelDomain​

Extracts the the top-level domain from a URL.

topLevelDomain(url)

Arguments

Note

The URL can be specified with or without a protocol. Examples:

svn+ssh://some.svn-hosting.com:80/repo/trunk
some.svn-hosting.com:80/repo/trunk
https://clickhouse.com/time/

Returned values

  • Domain name if the input string can be parsed as a URL. Otherwise, an empty string. String.

Example

Query:

SELECT topLevelDomain('svn+ssh://www.some.svn-hosting.com:80/repo/trunk');

Result:

┌─topLevelDomain('svn+ssh://www.some.svn-hosting.com:80/repo/trunk')─┐
│ com                                                                │
└────────────────────────────────────────────────────────────────────┘

topLevelDomainRFC​

Extracts the the top-level domain from a URL. Similar to topLevelDomain, but conforms to RFC 3986.

topLevelDomainRFC(url)

Arguments

Note

The URL can be specified with or without a protocol. Examples:

svn+ssh://some.svn-hosting.com:80/repo/trunk
some.svn-hosting.com:80/repo/trunk
https://clickhouse.com/time/

Returned values

  • Domain name if the input string can be parsed as a URL. Otherwise, an empty string. String.

Example

Query:

SELECT topLevelDomain('http://foo:foo%41bar@foo.com'), topLevelDomainRFC('http://foo:foo%41bar@foo.com');

Result:

┌─topLevelDomain('http://foo:foo%41bar@foo.com')─┬─topLevelDomainRFC('http://foo:foo%41bar@foo.com')─┐
│                                                │ com                                               │
└────────────────────────────────────────────────┴───────────────────────────────────────────────────┘

firstSignificantSubdomain​

Returns the "first significant subdomain". The first significant subdomain is a second-level domain for com, net, org, or co, otherwise it is a third-level domain. For example, firstSignificantSubdomain ('https://news.clickhouse.com/') = 'clickhouse', firstSignificantSubdomain ('https://news.clickhouse.com.tr/') = 'clickhouse'. The list of "insignificant" second-level domains and other implementation details may change in the future.

Syntax

firstSignificantSubdomain(url)

Arguments

Returned value

  • The first significant subdomain. String.

Example

Query:

SELECT firstSignificantSubdomain('http://www.example.com/a/b/c?a=b')

Result:

┌─firstSignificantSubdomain('http://www.example.com/a/b/c?a=b')─┐
│ example                                                       │
└───────────────────────────────────────────────────────────────┘

firstSignificantSubdomainRFC​

Returns the "first significant subdomain". The first significant subdomain is a second-level domain for com, net, org, or co, otherwise it is a third-level domain. For example, firstSignificantSubdomain ('https://news.clickhouse.com/') = 'clickhouse', firstSignificantSubdomain ('https://news.clickhouse.com.tr/') = 'clickhouse'. The list of "insignificant" second-level domains and other implementation details may change in the future. Similar to firstSignficantSubdomain but conforms to RFC 1034.

Syntax

firstSignificantSubdomainRFC(url)

Arguments

Returned value

  • The first significant subdomain. String.

Example

Query:

SELECT
    firstSignificantSubdomain('http://user:password@example.com:8080/path?query=value#fragment'),
    firstSignificantSubdomainRFC('http://user:password@example.com:8080/path?query=value#fragment');

Result:

┌─firstSignificantSubdomain('http://user:password@example.com:8080/path?query=value#fragment')─┬─firstSignificantSubdomainRFC('http://user:password@example.com:8080/path?query=value#fragment')─┐
│                                                                                              │ example                                                                                         │
└──────────────────────────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────────────────────────────────────────────────────────┘

cutToFirstSignificantSubdomain​

Returns the part of the domain that includes top-level subdomains up to the "first significant subdomain".

Syntax

cutToFirstSignificantSubdomain(url)

Arguments

Returned value

  • Part of the domain that includes top-level subdomains up to the first significant subdomain if possible, otherwise returns an empty string. String.

Example

Query:

SELECT
    cutToFirstSignificantSubdomain('https://news.clickhouse.com.tr/'),
    cutToFirstSignificantSubdomain('www.tr'),
    cutToFirstSignificantSubdomain('tr');

Result:

┌─cutToFirstSignificantSubdomain('https://news.clickhouse.com.tr/')─┬─cutToFirstSignificantSubdomain('www.tr')─┬─cutToFirstSignificantSubdomain('tr')─┐
│ clickhouse.com.tr                                                 │ tr                                       │                                      │
└───────────────────────────────────────────────────────────────────┴──────────────────────────────────────────┴──────────────────────────────────────┘

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

Returned value

  • Part of the domain that includes top-level subdomains up to the first significant subdomain if possible, otherwise returns an empty string. String.

Example

Query:

SELECT
    cutToFirstSignificantSubdomain('http://user:password@example.com:8080'),
    cutToFirstSignificantSubdomainRFC('http://user:password@example.com:8080');

Result:

┌─cutToFirstSignificantSubdomain('http://user:password@example.com:8080')─┬─cutToFirstSignificantSubdomainRFC('http://user:password@example.com:8080')─┐
│                                                                         │ example.com                                                                │
└─────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────┘

cutToFirstSignificantSubdomainWithWWW​

Returns the part of the domain that includes top-level subdomains up to the "first significant subdomain", without stripping www.

Syntax

cutToFirstSignificantSubdomainWithWWW(url)

Arguments

Returned value

  • 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.

Example

Query:

SELECT
    cutToFirstSignificantSubdomainWithWWW('https://news.clickhouse.com.tr/'),
    cutToFirstSignificantSubdomainWithWWW('www.tr'),
    cutToFirstSignificantSubdomainWithWWW('tr');

Result:

┌─cutToFirstSignificantSubdomainWithWWW('https://news.clickhouse.com.tr/')─┬─cutToFirstSignificantSubdomainWithWWW('www.tr')─┬─cutToFirstSignificantSubdomainWithWWW('tr')─┐
│ clickhouse.com.tr                                                        │ www.tr                                          │                                             │
└──────────────────────────────────────────────────────────────────────────┴─────────────────────────────────────────────────┴─────────────────────────────────────────────┘

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

cutToFirstSignificantSubdomainWithWWW(url)

Arguments

Returned value

  • 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.

Example

Query:

SELECT
    cutToFirstSignificantSubdomainWithWWW('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy'),
    cutToFirstSignificantSubdomainWithWWWRFC('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy');

Result:

┌─cutToFirstSignificantSubdomainWithWWW('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy')─┬─cutToFirstSignificantSubdomainWithWWWRFC('http:%2F%2Fwwwww.nova@mail.ru/economicheskiy')─┐
│                                                                                       │ mail.ru                                                                                  │
└───────────────────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────┘

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_path>/var/lib/clickhouse/top_level_domains/</top_level_domains_path> -->
<top_level_domains_lists>
    <!-- https://publicsuffix.org/list/public_suffix_list.dat -->
    <public_suffix_list>public_suffix_list.dat</public_suffix_list>
    <!-- NOTE: path is under top_level_domains_path -->
</top_level_domains_lists>

Syntax

cutToFirstSignificantSubdomain(url, tld)

Arguments

  • url — URL. String.
  • tld — Custom TLD list name. String.

Returned value

  • Part of the domain that includes top-level subdomains up to the first significant subdomain. String.

Example

Query:

SELECT cutToFirstSignificantSubdomainCustom('bar.foo.there-is-no-such-domain', 'public_suffix_list');

Result:

┌─cutToFirstSignificantSubdomainCustom('bar.foo.there-is-no-such-domain', 'public_suffix_list')─┐
│ foo.there-is-no-such-domain                                                                   │
└───────────────────────────────────────────────────────────────────────────────────────────────┘

See Also

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.

Syntax

cutToFirstSignificantSubdomainRFC(url, tld)

Arguments

  • url — URL. String.
  • tld — Custom TLD list name. String.

Returned value

  • Part of the domain that includes top-level subdomains up to the first significant subdomain. String.

See Also

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_path>/var/lib/clickhouse/top_level_domains/</top_level_domains_path> -->
<top_level_domains_lists>
    <!-- https://publicsuffix.org/list/public_suffix_list.dat -->
    <public_suffix_list>public_suffix_list.dat</public_suffix_list>
    <!-- NOTE: path is under top_level_domains_path -->
</top_level_domains_lists>

Syntax

cutToFirstSignificantSubdomainCustomWithWWW(url, tld)

Arguments

  • url — URL. String.
  • tld — Custom TLD list name. String.

Returned value

  • Part of the domain that includes top-level subdomains up to the first significant subdomain without stripping www. String.

Example

Query:

SELECT cutToFirstSignificantSubdomainCustomWithWWW('www.foo', 'public_suffix_list');

Result:

┌─cutToFirstSignificantSubdomainCustomWithWWW('www.foo', 'public_suffix_list')─┐
│ www.foo                                                                      │
└──────────────────────────────────────────────────────────────────────────────┘

See Also

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.

Syntax

cutToFirstSignificantSubdomainCustomWithWWWRFC(url, tld)

Arguments

  • url — URL. String.
  • tld — Custom TLD list name. String.

Returned value

  • Part of the domain that includes top-level subdomains up to the first significant subdomain without stripping www. String.

See Also

firstSignificantSubdomainCustom​

Returns the first significant subdomain. Accepts customs TLD list name. Can be useful if you need fresh TLD list or you have custom.

Configuration example:

<!-- <top_level_domains_path>/var/lib/clickhouse/top_level_domains/</top_level_domains_path> -->
<top_level_domains_lists>
    <!-- https://publicsuffix.org/list/public_suffix_list.dat -->
    <public_suffix_list>public_suffix_list.dat</public_suffix_list>
    <!-- NOTE: path is under top_level_domains_path -->
</top_level_domains_lists>

Syntax

firstSignificantSubdomainCustom(url, tld)

Arguments

  • url — URL. String.
  • tld — Custom TLD list name. String.

Returned value

  • First significant subdomain. String.

Example

Query:

SELECT firstSignificantSubdomainCustom('bar.foo.there-is-no-such-domain', 'public_suffix_list');

Result:

┌─firstSignificantSubdomainCustom('bar.foo.there-is-no-such-domain', 'public_suffix_list')─┐
│ foo                                                                                      │
└──────────────────────────────────────────────────────────────────────────────────────────┘

See Also

firstSignificantSubdomainCustomRFC​

Returns the first significant subdomain. Accepts customs TLD list name. Can be useful if you need fresh TLD list or you have custom. Similar to firstSignificantSubdomainCustom but conforms to RFC 3986.

Syntax

firstSignificantSubdomainCustomRFC(url, tld)

Arguments

  • url — URL. String.
  • tld — Custom TLD list name. String.

Returned value

  • First significant subdomain. String.

See Also

port​

Returns the port or default_port if the URL contains no port or cannot be parsed.

Syntax

port(url [, default_port = 0])

Arguments

  • url — URL. String.
  • default_port — The default port number to be returned. UInt16.

Returned value

  • Port or the default port if there is no port in the URL or in case of a validation error. UInt16.

Example

Query:

SELECT port('http://paul@www.example.com:80/');

Result:

┌─port('http://paul@www.example.com:80/')─┐
│                                      80 │
└─────────────────────────────────────────┘

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 = 0])

Arguments

  • url — URL. String.
  • default_port — The default port number to be returned. UInt16.

Returned value

  • Port or the default port if there is no port in the URL or in case of a validation error. UInt16.

Example

Query:

SELECT
    port('http://user:password@example.com:8080'),
    portRFC('http://user:password@example.com:8080');

Result:

┌─port('http://user:password@example.com:8080')─┬─portRFC('http://user:password@example.com:8080')─┐
│                                             0 │                                             8080 │
└───────────────────────────────────────────────┴──────────────────────────────────────────────────┘

path​

Returns the path without query string.

Example: /top/news.html.

pathFull​

The same as above, but including query string and fragment.

Example: /top/news.html?page=2#comments.

protocol​

Extracts the protocol from a URL.

Syntax

protocol(url)

Arguments

  • url — URL to extract protocol from. String.

Returned value

  • Protocol, or an empty string if it cannot be determined. String.

Example

Query:

SELECT protocol('https://clickhouse.com/');

Result:

┌─protocol('https://clickhouse.com/')─┐
│ https                               │
└─────────────────────────────────────┘

queryString​

Returns the query string without the initial question mark, # and everything after #.

Example: page=1&lr=213.

fragment​

Returns the fragment identifier without the initial hash symbol.

queryStringAndFragment​

Returns the query string and fragment identifier.

Example: page=1#29390.

extractURLParameter(url, name)​

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.

extractURLParameters(url)​

Returns an array of name=value strings corresponding to the URL parameters. The values are not decoded.

extractURLParameterNames(url)​

Returns an array of name strings corresponding to the names of URL parameters. The values are not decoded.

URLHierarchy(url)​

Returns an array containing the URL, truncated at the end by the symbols /,? in the path and query-string. Consecutive separator characters are counted as one. The cut is made in the position after all the consecutive separator characters.

URLPathHierarchy(url)​

The same as above, but without the protocol and host in the result. The / element (root) is not included.

URLPathHierarchy('https://example.com/browse/CONV-6788') =
[
    '/browse/',
    '/browse/CONV-6788'
]

encodeURLComponent(url)​

Returns the encoded URL.

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 │
└──────────────────────────────────────────────────────────┘

decodeURLComponent(url)​

Returns the decoded URL.

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; │
└────────────────────────────────────────┘

encodeURLFormComponent(url)​

Returns the encoded URL. Follows rfc-1866, space( ) is encoded as plus(+).

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 │
└───────────────────────────────────────────────────────────┘

decodeURLFormComponent(url)​

Returns the decoded URL. Follows rfc-1866, plain plus(+) is decoded as space( ).

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 │
└───────────────────────────────────────────┘

netloc​

Extracts network locality (username:password@host:port) from a URL.

Syntax

netloc(url)

Arguments

Returned value

  • username:password@host:port. String.

Example

Query:

SELECT netloc('http://paul@www.example.com:80/');

Result:

┌─netloc('http://paul@www.example.com:80/')─┐
│ paul@www.example.com:80                   │
└───────────────────────────────────────────┘

Functions that remove part of a URL​

If the URL does not have anything similar, the URL remains unchanged.

cutWWW​

Removes leading www. (if present) from the URL's domain.

cutQueryString​

Removes query string, including the question mark.

cutFragment​

Removes the fragment identifier, including the number sign.

cutQueryStringAndFragment​

Removes the query string and fragment identifier, including the question mark and number sign.

cutURLParameter(url, name)​

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.

Example

Query:

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;

Result:

┌─url_without_a────────────────┬─url_without_c_and_e──────┐
│ http://bigmir.net/?c=d&e=f#g │ http://bigmir.net/?a=b#g │
└──────────────────────────────┴──────────────────────────┘

cutFragment​

Introduced in: v1.1

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                            │
└────────────────────────────────────────────────────────────────┘

cutQueryString​

Introduced in: v1.1

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                                         │
└──────────────────────────────────────────────────────────────────────────┘

cutQueryStringAndFragment​

Introduced in: v1.1

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                                                             │
└─────────────────────────────────────────────────────────────────────────────────────┘

cutToFirstSignificantSubdomain​

Introduced in: v

Returns the part of the domain that includes top-level subdomains up to the "first significant subdomain" (see documentation of the firstSignificantSubdomain).

Syntax

Arguments

  • None. Returned value

Examples

cutToFirstSignificantSubdomain1

SELECT cutToFirstSignificantSubdomain('https://news.clickhouse.com.tr/')

cutToFirstSignificantSubdomain2

SELECT cutToFirstSignificantSubdomain('www.tr')

cutToFirstSignificantSubdomain3

SELECT cutToFirstSignificantSubdomain('tr')

cutToFirstSignificantSubdomainCustom​

Introduced in: v

Returns the part of the domain that includes top-level subdomains up to the first significant subdomain. Accepts custom TLD list name.

Can be useful if you need fresh TLD list or you have custom.

Syntax

Arguments

  • None. Returned value

Examples

cutToFirstSignificantSubdomainCustom

SELECT cutToFirstSignificantSubdomainCustom('bar.foo.there-is-no-such-domain', 'public_suffix_list');

cutToFirstSignificantSubdomainCustomRFC​

Introduced in: v

Similar to cutToFirstSignificantSubdomainCustom but follows stricter rules according to RFC 3986.

Syntax

Arguments

  • None. Returned value

Examples

cutToFirstSignificantSubdomainCustomWithWWW​

Introduced in: v

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 from config.

Can be useful if you need fresh TLD list or you have custom.

Syntax

Arguments

  • None. Returned value

Examples

cutToFirstSignificantSubdomainCustomWithWWW

SELECT cutToFirstSignificantSubdomainCustomWithWWW('www.foo', 'public_suffix_list')

cutToFirstSignificantSubdomainCustomWithWWWRFC​

Introduced in: v

Similar to cutToFirstSignificantSubdomainCustomWithWWW but follows stricter rules according to RFC 3986.

Syntax

Arguments

  • None. Returned value

Examples

cutToFirstSignificantSubdomainRFC​

Introduced in: v

Similar to cutToFirstSignificantSubdomain but follows stricter rules to be compatible with RFC 3986 and less performant.

Syntax

Arguments

  • None. Returned value

Examples

cutToFirstSignificantSubdomainWithWWW​

Introduced in: v

Returns the part of the domain that includes top-level subdomains up to the "first significant subdomain", without stripping "www".

Syntax

Arguments

  • None. Returned value

Examples

cutToFirstSignificantSubdomainWithWWWRFC​

Introduced in: v

Similar to cutToFirstSignificantSubdomainWithWWW but follows stricter rules to be compatible with RFC 3986 and less performant.

Syntax

Arguments

  • None. Returned value

Examples

cutURLParameter​

Introduced in: v1.1

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 │
└──────────────────────────────┴──────────────────────────┘

cutWWW​

Introduced in: v1.1

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               │
└────────────────────────────────────────────────────────────┘

decodeURLComponent​

Introduced in: v1.1

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; │
└────────────────────────────────────────┘

decodeURLFormComponent​

Introduced in: v1.1

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 │
└───────────────────────────────────────────┘

domain​

Introduced in: v1.1

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                                   │
└────────────────────────────────────────────────────────┘

domainRFC​

Introduced in: v22.10

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                                                                  │
└───────────────────────────────────────────────────────────────────────────┴──────────────────────────────────────────────────────────────────────────────┘

domainWithoutWWW​

Introduced in: v1.1

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                                         │
└─────────────────────────────────────────────────────┘

domainWithoutWWWRFC​

Introduced in: v1.1

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                                                                                │
└─────────────────────────────────────────────────────────────────────────────────────────┴────────────────────────────────────────────────────────────────────────────────────────────┘

encodeURLComponent​

Introduced in: v22.3

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 │
└──────────────────────────────────────────────────────────┘

encodeURLFormComponent​

Introduced in: v22.3

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 │
└───────────────────────────────────────────────────────────┘

extractURLParameter​

Introduced in: v1.1

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                   │
└──────────────────────────┘

extractURLParameterNames​

Introduced in: v1.1

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']      │
└──────────────────────────┘

extractURLParameters​

Introduced in: v1.1

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'] │
└───────────────────────────────────┘

firstSignificantSubdomain​

Introduced in: v

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.clickhouse.com/') = 'clickhouse', firstSignificantSubdomain ('https://news.clickhouse.com.tr/') = 'clickhouse'.

The list of "insignificant" second-level domains and other implementation details may change in the future.

Syntax

Arguments

  • None. Returned value

Examples

firstSignificantSubdomain

SELECT firstSignificantSubdomain('https://news.clickhouse.com/')

firstSignificantSubdomainRFC​

Introduced in: v

Returns the "first significant subdomain" according to RFC 1034.

Syntax

Arguments

  • None. Returned value

Examples

fragment​

Introduced in: v1.1

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://clickhouse.com/docs/getting-started/quick-start/cloud#1-create-a-clickhouse-service');

┌─fragment('http⋯ouse-service')─┐
│ 1-create-a-clickhouse-service │
└───────────────────────────────┘

netloc​

Introduced in: v20.5

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  │
└──────────────────────────┘

path​

Introduced in: v1.1

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://clickhouse.com/docs/sql-reference/functions/url-functions/?query=value');

┌─path('https://clickhouse.com/en/sql-reference/functions/url-functions/?query=value')─┐
│ /docs/sql-reference/functions/url-functions/                                         │
└──────────────────────────────────────────────────────────────────────────────────────┘

pathFull​

Introduced in: v1.1

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://clickhouse.com/docs/sql-reference/functions/url-functions/?query=value#section');

┌─pathFull('https://clickhouse.com⋯unctions/?query=value#section')─┐
│ /docs/sql-reference/functions/url-functions/?query=value#section │
└──────────────────────────────────────────────────────────────────┘

port​

Introduced in: v20.5

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://clickhouse.com:8443/docs'), port('https://clickhouse.com/docs', 443);

┌─port('https://clickhouse.com:8443/docs')─┬─port('https://clickhouse.com/docs', 443)─┐
│                                     8443 │                                      443 │
└──────────────────────────────────────────┴──────────────────────────────────────────┘

portRFC​

Introduced in: v22.10

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 │
└──────────────────────────┴──────────────────────────┘

protocol​

Introduced in: v1.1

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://clickhouse.com/');

┌─protocol('https://clickhouse.com/')─┐
│ https                               │
└─────────────────────────────────────┘

queryString​

Introduced in: v1.1

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://clickhouse.com/docs?query=value&param=123#section');

┌─queryString(⋯3#section')─┐
│ query=value&param=123    │
└──────────────────────────┘

queryStringAndFragment​

Introduced in: v1.1

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://clickhouse.com/docs?query=value&param=123#section');

┌─queryStringAnd⋯=123#section')─┐
│ query=value&param=123#section │
└───────────────────────────────┘

topLevelDomain​

Introduced in: v1.1

Extracts the the top-level domain from a URL.

Note

The URL can be specified with or without a protocol. For examples

svn+ssh://some.svn-hosting.com:80/repo/trunk
some.svn-hosting.com:80/repo/trunk
https://clickhouse.com/time/
    

**Syntax**

```sql
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                                                                │
└────────────────────────────────────────────────────────────────────┘

topLevelDomainRFC​

Introduced in: v22.10

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                                               │
└────────────────────────────────────────────────┴───────────────────────────────────────────────────┘