Functions for working with URLs - ClickHouse Documentation

Overview

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.

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

Query

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

Response

['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

Query

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

Response

['/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

Query

SELECT cutFragment('http://example.com/path?query=value#fragment123');

Response

┌─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

Query

SELECT cutQueryString('http://example.com/path?query=value&param=123#fragment');

Response

┌─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

Query

SELECT cutQueryStringAndFragment('http://example.com/path?query=value&param=123#fragment');

Response

┌─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

Query

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

Response

┌─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

Query

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

Response

foo.there-is-no-such-domain

cutToFirstSignificantSubdomainCustomRFC

Introduced in: v22.10.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. Similar to cutToFirstSignificantSubdomainCustom 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

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

Query

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

Response

┌─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

Query

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

Response

┌─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

Query

SELECT cutToFirstSignificantSubdomainCustomWithWWWRFC('https://www.subdomain.example.custom', 'public_suffix_list')

Response

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

Query

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

Response

┌─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

Query

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

Response

┌─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

Query

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

Response

┌─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

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;

Response

┌─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

Query

SELECT cutWWW('http://www.example.com/path?query=value#fragment');

Response

┌─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

Query

SELECT decodeURLComponent('http://127.0.0.1:8123/?query=SELECT%201%3B') AS DecodedURL;

Response

┌─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

Query

SELECT decodeURLFormComponent('http://127.0.0.1:8123/?query=SELECT%201+2%2B3') AS DecodedURL;

Response

┌─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

Query

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

Response

┌─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

Query

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

Response

┌─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

Query

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

Response

┌─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

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

Response

┌─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

Query

SELECT encodeURLComponent('http://127.0.0.1:8123/?query=SELECT 1;') AS EncodedURL;

Response

┌─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

Query

SELECT encodeURLFormComponent('http://127.0.0.1:8123/?query=SELECT 1 2+3') AS EncodedURL;

Response

┌─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

Query

SELECT extractURLParameter('http://example.com/?param1=value1&param2=value2', 'param1');

Response

┌─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

Query

SELECT extractURLParameterNames('http://example.com/?param1=value1&param2=value2');

Response

┌─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

Query

SELECT extractURLParameters('http://example.com/?param1=value1&param2=value2');

Response

┌─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

Query

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

Response

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

Query

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

Response

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

Query

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

Response

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

Query

SELECT fragment('https://clickhouse.com/docs/getting-started/quick-start/cloud#1-create-a-clickhouse-service');

Response

┌─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

Query

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

Response

┌─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

Query

SELECT path('https://clickhouse.com/docs/sql-reference/functions/url-functions/?query=value');

Response

┌─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

Query

SELECT pathFull('https://clickhouse.com/docs/sql-reference/functions/url-functions/?query=value#section');

Response

┌─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

Query

SELECT port('https://clickhouse.com:8443/docs'), port('https://clickhouse.com/docs', 443);

Response

┌─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

Query

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

Response

┌─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

Query

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

Response

┌─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

Query

SELECT queryString('https://clickhouse.com/docs?query=value&param=123#section');

Response

┌─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

Query

SELECT queryStringAndFragment('https://clickhouse.com/docs?query=value&param=123#section');

Response

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

topLevelDomain

Introduced in: v1.1.0 Extracts the the top-level domain from a URL.

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

Query

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

Response

┌─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

Query

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

Response

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

SQL Reference

Documentation Index

​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