Functions for working with URLs

Overview

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:

Symbol	non-`RFC`	`RFC`
' '	✗	✗
\t	✗	✗
<	✗	✗
>	✗	✗
%	✗	✔*
{	✗	✗
}	✗	✗
\|	✗	✗
\\	✗	✗
^	✗	✗
~	✗	✔*
[	✗	✗
]	✗	✔
;	✗	✔*
=	✗	✔*
&	✗	✔*

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

There are two types of URL functions:

Functions that extract parts of a URL. If the relevant part isn't present in a URL, an empty string is returned.
Functions that remove part of a URL. If the URL does not have anything similar, the URL remains unchanged.

Note

The functions below are generated from the system.functions system table.

URLHierarchy

Introduced in: v1.1.0

Returns an array containing the URL, truncated at the end by the symbols /, ? and # in the path and query string. Consecutive separator characters are counted as one. The result includes the protocol and host as the first element, with progressively longer paths forming a hierarchy.

Syntax

URLHierarchy(url)

Arguments

url — The URL to process. String

Returned value

Returns an array of progressively longer URLs forming a hierarchy. Array(String)

Examples

Basic usage

SELECT URLHierarchy('https://example.com/a/b?c=1')

['https://example.com/','https://example.com/a/','https://example.com/a/b','https://example.com/a/b?c=1']

URLPathHierarchy

Introduced in: v1.1.0

Returns an array containing the path component of the URL, truncated at the end by the symbols /, ? and #. Unlike URLHierarchy, the result does not include the protocol and host — it starts from the path. Consecutive separator characters are counted as one.

Syntax

URLPathHierarchy(url)

Arguments

url — The URL to process. String

Returned value

Returns an array of progressively longer URL path components forming a hierarchy. Array(String)

Examples

Basic usage

SELECT URLPathHierarchy('https://example.com/a/b?c=1')

['/a/','/a/b','/a/b?c=1']

cutFragment

Introduced in: v1.1.0

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

cutQueryString

Introduced in: v1.1.0

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&param=123#fragment');

┌─cutQueryString('http://example.com/path?query=value&param=123#fragment')─┐
│ http://example.com/path#fragment                                         │
└──────────────────────────────────────────────────────────────────────────┘

cutQueryStringAndFragment

Introduced in: v1.1.0

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&param=123#fragment');

┌─cutQueryStringAndFragment('http://example.com/path?query=value&param=123#fragment')─┐
│ http://example.com/path                                                             │
└─────────────────────────────────────────────────────────────────────────────────────┘

cutToFirstSignificantSubdomain

Introduced in: v1.1.0

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.clickhouse.com.tr/'),
    cutToFirstSignificantSubdomain('www.tr'),
    cutToFirstSignificantSubdomain('tr');

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

cutToFirstSignificantSubdomainCustom

Introduced in: v21.1.0

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

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

cutToFirstSignificantSubdomainCustomRFC

Introduced in: v22.10.0

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

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

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

cutToFirstSignificantSubdomainCustomWithWWW

Introduced in: v21.1.0

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**

```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 ClickHouse.

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

cutToFirstSignificantSubdomainCustomWithWWWRFC

Introduced in: v22.10.0

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_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**

```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 ClickHouse.

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

cutToFirstSignificantSubdomainRFC

Introduced in: v22.10.0

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

cutToFirstSignificantSubdomainWithWWW

Introduced in: v20.12.0

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.clickhouse.com.tr/'),
    cutToFirstSignificantSubdomainWithWWW('www.tr'),
    cutToFirstSignificantSubdomainWithWWW('tr');

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

cutToFirstSignificantSubdomainWithWWWRFC

Introduced in: v22.10.0

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

cutURLParameter

Introduced in: v1.1.0

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. String
name — Name of URL parameter. String or Array(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 │
└──────────────────────────────┴──────────────────────────┘

cutWWW

Introduced in: v1.1.0

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

decodeURLComponent

Introduced in: v1.1.0

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

decodeURLFormComponent

Introduced in: v1.1.0

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

domain

Introduced in: v1.1.0

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

domainRFC

Introduced in: v22.10.0

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

domainWithoutWWW

Introduced in: v1.1.0

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

domainWithoutWWWRFC

Introduced in: v1.1.0

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

encodeURLComponent

Introduced in: v22.3.0

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

encodeURLFormComponent

Introduced in: v22.3.0

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

extractURLParameter

Introduced in: v1.1.0

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

url — URL. String
name — Parameter name. String

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

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&param2=value2');

┌─extractURLPa⋯m2=value2')─┐
│ ['param1','param2']      │
└──────────────────────────┘

extractURLParameters

Introduced in: v1.1.0

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&param2=value2');

┌─extractURLParame⋯&param2=value2')─┐
│ ['param1=value1','param2=value2'] │
└───────────────────────────────────┘

firstSignificantSubdomain

Introduced in: v1.1.0

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

firstSignificantSubdomain(url)

Arguments

None.

Returned value

Examples

firstSignificantSubdomain

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

firstSignificantSubdomainCustom

Introduced in: v21.1.0

Returns the first significant subdomain of a URL using a custom TLD (Top-Level Domain) list. The custom TLD list name refers to a configuration that defines which domain suffixes should be treated as top-level domains. This is useful for non-standard TLD hierarchies. The function uses a simplified URL parsing algorithm that assumes the protocol and everything following are stripped.

Syntax

firstSignificantSubdomainCustom(url, tld_list_name)

Arguments

url — The URL to extract the subdomain from. String
tld_list_name — Name of the custom TLD list from the configuration. String

Returned value

Returns the first significant subdomain. String

Examples

Basic usage

SELECT firstSignificantSubdomainCustom('https://news.example.com', 'public_suffix_list')

example

firstSignificantSubdomainCustomRFC

Introduced in: v22.10.0

Similar to firstSignificantSubdomainCustom but uses RFC 3986 compliant URL parsing instead of the simplified algorithm.

Syntax

firstSignificantSubdomainCustomRFC(url, tld_list_name)

Arguments

url — The URL to extract the subdomain from. String
tld_list_name — Name of the custom TLD list from the configuration. String

Returned value

Returns the first significant subdomain. String

Examples

Basic usage

SELECT firstSignificantSubdomainCustomRFC('https://news.example.com', 'public_suffix_list')

example

firstSignificantSubdomainRFC

Introduced in: v22.10.0

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

Syntax

firstSignificantSubdomainRFC(url)

Arguments

None.

Returned value

Examples

fragment

Introduced in: v1.1.0

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://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.0

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

path

Introduced in: v1.1.0

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://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.0

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://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.0

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

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

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

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

queryString

Introduced in: v1.1.0

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

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

queryStringAndFragment

Introduced in: v1.1.0

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

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

topLevelDomain

Introduced in: v1.1.0

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://clickhouse.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                                                                │
└────────────────────────────────────────────────────────────────────┘

topLevelDomainRFC

Introduced in: v22.10.0

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

Overview​

URLHierarchy​

URLPathHierarchy​

cutFragment​

cutQueryString​

cutQueryStringAndFragment​

cutToFirstSignificantSubdomain​

cutToFirstSignificantSubdomainCustom​

cutToFirstSignificantSubdomainCustomRFC​

cutToFirstSignificantSubdomainCustomWithWWW​

cutToFirstSignificantSubdomainCustomWithWWWRFC​

cutToFirstSignificantSubdomainRFC​

cutToFirstSignificantSubdomainWithWWW​

cutToFirstSignificantSubdomainWithWWWRFC​

cutURLParameter​

cutWWW​

decodeURLComponent​

decodeURLFormComponent​

domain​

domainRFC​

domainWithoutWWW​

domainWithoutWWWRFC​

encodeURLComponent​

encodeURLFormComponent​

extractURLParameter​

extractURLParameterNames​

extractURLParameters​

firstSignificantSubdomain​

firstSignificantSubdomainCustom​

firstSignificantSubdomainCustomRFC​

firstSignificantSubdomainRFC​

fragment​

netloc​

path​

pathFull​

port​

portRFC​

protocol​

queryString​

queryStringAndFragment​

topLevelDomain​

topLevelDomainRFC​

Overview

URLHierarchy

URLPathHierarchy

cutFragment

cutQueryString

cutQueryStringAndFragment

cutToFirstSignificantSubdomain

cutToFirstSignificantSubdomainCustom

cutToFirstSignificantSubdomainCustomRFC

cutToFirstSignificantSubdomainCustomWithWWW

cutToFirstSignificantSubdomainCustomWithWWWRFC

cutToFirstSignificantSubdomainRFC

cutToFirstSignificantSubdomainWithWWW

cutToFirstSignificantSubdomainWithWWWRFC

cutURLParameter

cutWWW

decodeURLComponent

decodeURLFormComponent

domain

domainRFC

domainWithoutWWW

domainWithoutWWWRFC

encodeURLComponent

encodeURLFormComponent

extractURLParameter

extractURLParameterNames

extractURLParameters

firstSignificantSubdomain

firstSignificantSubdomainCustom

firstSignificantSubdomainCustomRFC

firstSignificantSubdomainRFC

fragment

netloc

path

pathFull

port

portRFC

protocol

queryString

queryStringAndFragment

topLevelDomain

topLevelDomainRFC