Docs

ByteHouse Cloud Data Warehouse Version

Function reference

IP & URL functions

IPv4NumToString

Accepts a UInt32 number. Interprets it as a big-endian IPv4 address. Returns a string containing the corresponding IPv4 address, in the format ABCd (decimal numbers separated by dots).
Syntax:

IPv4NumToString(num)

Parameter:

num – a UInt32 number.

Return value:

A string representing the IPv4 address.

Example:

SELECT toIPv4('116.106.34.242') as ipv4, toTypeName(ipv4), IPv4NumToString(ipv4) as ipv4_string, toTypeName(ipv4_string)

┌─ipv4───────────┬─toTypeName(toIPv4('116.106.34.242'))─┬─ipv4_string────┬─toTypeName(IPv4NumToString(toIPv4('116.106.34.242')))─┐ 
│ 242.34.106.116 │ IPv4                                 │ 116.106.34.242 │ String                                                │ 
└────────────────┴──────────────────────────────────────┴────────────────┴───────────────────────────────────────────────────────┘

IPv4NumToStringClassC

Similar to IPv4NumToString, but uses xxx instead of the last octet.
Syntax:

IPv4NumToStringClassC(num)

Parameter:

num – a UInt32 number.

Return value:

A string representing the IPv4 address, but uses xxx instead of the last octet.

Example:

SELECT toIPv4('116.106.34.242') as ipv4, toTypeName(ipv4), IPv4NumToStringClassC(ipv4) as ipv4_string, toTypeName(ipv4_string)

┌─ipv4───────────┬─toTypeName(toIPv4('116.106.34.242'))─┬─ipv4_string────┬─toTypeName(IPv4NumToStringClassC(toIPv4('116.106.34.242')))─┐ 
│ 242.34.106.116 │ IPv4                                 │ 116.106.34.xxx │ String                                                      │ 
└────────────────┴──────────────────────────────────────┴────────────────┴─────────────────────────────────────────────────────────────┘

IPv4StringToNum

The inverse function of IPv4NumToString. If the IPv4 address format is invalid, returns 0.
Syntax:

IPv4StringToNum(s)

Parameter:

s – IPv4 address represented as a string.

Return value:

UInt32。

Example:

SELECT IPv4StringToNum('116.106.34.242') as ipv4, toTypeName(ipv4), IPv4NumToString(ipv4) as ipv4_string, toTypeName(ipv4_string)

┌─ipv4───────┬─toTypeName(IPv4StringToNum('116.106.34.242'))─┬─ipv4_string────┬─toTypeName(IPv4NumToString(IPv4StringToNum('116.106.34.242')))─┐ 
│ 1953112818 │ UInt32                                        │ 116.106.34.242 │ String                                                         │ 
└────────────┴───────────────────────────────────────────────┴────────────────┴────────────────────────────────────────────────────────────────┘

IPv4ToIPv6

Accepts a UInt32 number. Interprets it as a big endian IPv4 address. Returns a FixedString(16) value containing the IPv6 address in binary format.
Syntax:

IPv4ToIPv6(x)

Parameter:

x – a UInt32 number

Return value:

IPv6 address in binary format. FixedString(16)

Example:

SELECT IPv4StringToNum('192.168.0.1') as ipv4, IPv6NumToString(IPv4ToIPv6(ipv4)) as ipv6_string

┌─ipv4───────┬─ipv6_string────────┐ 
│ 3232235521 │ ::ffff:192.168.0.1 │ 
└────────────┴────────────────────┘

IPv6NumToString

Accepts a FixedString(16) value containing the IPv6 address in binary format. Returns a string containing the text format of this address.
IPv6-mapped IPv4 addresses are output in the format ::ffff:111.222.33.44.
Syntax:

IPv6NumToString(x)

Parameter:

x – FixedString(16) value containing the IPv6 address in binary format

Return value:

A string representing the IPv6 address.

Example:

SELECT IPv6NumToString(toFixedString(unhex('2A0206B8000000000000000000000011'), 16)) AS addr;

┌─addr─────────┐ 
│ 2a02:6b8::11 │ 
└──────────────┘

IPv6StringToNum

The inverse function of IPv6NumToString. If the IPv6 address format is invalid, returns an empty byte string.
If the input string contains a valid IPv4 address, returns its IPv6 equivalent value. HEX can be uppercase or lowercase.
Syntax:

IPv6StringToNum(string)

Parameter:

string — IP address. String.

Return value:

IPv6 address in binary format. Type: FixedString(16).

Example:

SELECT addr, cutIPv6(IPv6StringToNum(addr), 0, 0) FROM (SELECT ['notaddress', '127.0.0.1', '1111::ffff'] AS addr) ARRAY JOIN addr;

┌─addr───────┬─cutIPv6(IPv6StringToNum(addr), 0, 0)─┐ 
│ notaddress │ ::                                   │ 
│ 127.0.0.1  │ ::                                   │ 
│ 1111::ffff │ 1111::ffff                           │ 
└────────────┴──────────────────────────────────────┘

cutIPv6

Accepts a FixedString(16) value containing the IPv6 address in binary format. Returns a string in text format containing the address with the specified number of bytes deleted.
Syntax:

cutIPv6(x, bytesToCutForIPv6, bytesToCutForIPv4)

Parameter:

x – FixedString(16) value containing the IPv6 address in binary format
bytesToCutForIPv6 – number of bytes to delete for IPv6 representation
bytesToCutForIPv4 – number of bytes to delete for IPv4 representation

Return value:
A Uint64 data type hash value.
Type: UInt64
Example:

WITH 
    IPv6StringToNum('2001:0DB8:AC10:FE01:FEED:BABE:CAFE:F00D') AS ipv6, 
    IPv4ToIPv6(IPv4StringToNum('192.168.0.1')) AS ipv4 
SELECT 
    cutIPv6(ipv6, 2, 0), 
    cutIPv6(ipv4, 0, 2)

┌─cutIPv6(ipv6, 2, 0)─────────────────┬─cutIPv6(ipv4, 0, 2)─┐ 
│ 2001:db8:ac10:fe01:feed:babe:cafe:0 │ ::ffff:192.168.0.0  │ 
└─────────────────────────────────────┴─────────────────────┘

toIPv4

Alias of IPv4StringToNum(). Accepts the string form of an IPv4 address and returns a value of IPv4 type, which is binary and equals the value returned by IPv4StringToNum().
Syntax:

toIPv4(string)

Parameter:

string — IP address. String.

Return value:

IPv4 type

Example:

WITH'171.225.130.45' as IPv4_string 
SELECT 
    toTypeName(IPv4StringToNum(IPv4_string)), 
    toTypeName(toIPv4(IPv4_string))

┌─toTypeName(IPv4StringToNum(IPv4_string))─┬─toTypeName(toIPv4(IPv4_string))─┐ 
│ UInt32                                   │ IPv4                            │ 
└──────────────────────────────────────────┴─────────────────────────────────┘

WITH'171.225.130.45' as IPv4_string 
SELECT 
    hex(IPv4StringToNum(IPv4_string)), 
    hex(toIPv4(IPv4_string))

┌─hex(IPv4StringToNum(IPv4_string))─┬─hex(toIPv4(IPv4_string))─┐ 
│ ABE1822D                          │ ABE1822D                 │ 
└───────────────────────────────────┴──────────────────────────┘

toIPv6

Converts an IPv6 address in string form to IPv6 type. If the IPv6 address format is invalid, then return null.
Similar to the IPv6StringToNum function, converts the IPv6 address to binary format.
Syntax

toIPv6(string)

Arguments

string— IP address. String

Return value

IP address. Type: IPv6.

Example

WITH '2001:438:ffff::407d:1bc1' AS IPv6_string 
SELECT 
    hex(IPv6StringToNum(IPv6_string)), 
    hex(toIPv6(IPv6_string));

┌─hex(IPv6StringToNum(IPv6_string))─┬─hex(toIPv6(IPv6_string))─────────┐ 
│ 20010438FFFF000000000000407D1BC1  │ 20010438FFFF000000000000407D1BC1 │ 
└───────────────────────────────────┴──────────────────────────────────┘

URLHierarchy

Returns an array that contains the URL, truncated at the end of the path and query string by the symbols /,?. Truncation. Consecutive delimiters are counted as one. Truncation is carried out at the location after all consecutive delimiters.
Syntax

URLHierarchy(URL)

Arguments

URL— URL。 Type: String.

Return value

Array that contains the URL

Example

SELECT URLHierarchy('https://example.com/browse/CONV-6788');

┌─URLHierarchy('https://example.com/browse/CONV-6788')────────────────────────────────────────────┐ 
│ ['https://example.com/', 'https://example.com/browse/', 'https://example.com/browse/CONV-6788'] │ 
└─────────────────────────────────────────────────────────────────────────────────────────────────┘

URLPathHierarchy

Same as above, but the result does not include protocol and host. Does not include the / element (root).
Syntax

URLPathHierarchy(URL)

Arguments

URL— URL。 Type: String.

Return value

Array that contains the URL

Example

SELECT URLPathHierarchy('https://example.com/browse/CONV-6788');

┌─URLPathHierarchy('https://example.com/browse/CONV-6788')─┐ 
│ ['/browse/', '/browse/CONV-6788']                        │ 
└──────────────────────────────────────────────────────────┘

cutFragment

Delete the fragment identifier. The number sign is also deleted.
Syntax

cutFragment(URL)

Arguments

URL– URL string

Return value

URL without fragment

Example

SELECT cutFragment('http://example.com#fragment')

┌─cutFragment('http://example.com#fragment')─┐ 
│ http://example.com                         │ 
└────────────────────────────────────────────┘

cutQueryString

Delete the query string. The question mark is also deleted.
Syntax

cutQueryString(URL)

Arguments

URL– URL string

Return value

URL without query

Example

SELECT cutQueryString('http://example.com/?page=1&lr=213')

┌─cutQueryString('http://example.com/?page=1&lr=213')─┐ 
│ http://example.com/                                 │ 
└─────────────────────────────────────────────────────┘

cutQueryStringAndFragment

Delete the query string and fragment identifier. The question mark and number sign are also deleted.
Syntax

cutQueryStringAndFragment(URL)

Arguments

URL– URL string

Return value

URL string without query string and fragment

Example

SELECT cutQueryStringAndFragment('http://example.com/?page=1&lr=213#fragment')

┌─cutQueryStringAndFragment('http://example.com/?page=1&lr=213#fragment')─┐ 
│ http://example.com/                                                     │ 
└─────────────────────────────────────────────────────────────────────────┘

cutToFirstSignificantSubdomain

Returns the domain name part that contains the top-level subdomain up to the "first significant subdomain".
Syntax

cutToFirstSignificantSubdomain(URL)

Arguments

URL– URL string

Return value

Subdomain string

Example

SELECT cutToFirstSignificantSubdomain('https://www.example.com.cn/')

┌─cutToFirstSignificantSubdomain('https://www.example.com.cn/')─┐ 
│ example.com.cn                                                │ 
└───────────────────────────────────────────────────────────────┘

cutURLParameter

Delete the "name" URL parameter (if present). This function works under the assumption that the encoding method of the parameter name in the URL is exactly the same as the encoding method of the parameter passed in.
Syntax

cutURLParameter(URL, name)

Arguments

URL– URL string
nameParameter name

Return value

URL string

Example

SELECT cutURLParameter('http://example.com/?page=1&lr=213','page')

┌─cutURLParameter('http://example.com/?page=1&lr=213', 'page')─┐ 
│ http://example.com/?lr=213                                   │ 
└──────────────────────────────────────────────────────────────┘

cutWWW

If present, delete at most one "www." from the beginning of the URL domain name.
Syntax

cutWWW(URL)

Arguments

URL– URL string
nameParameter name

Return value

URL string

Example

SELECT cutWWW('http://www.example.com/?page=1&lr=213')

┌─cutWWW('http://www.example.com/?page=1&lr=213')─┐ 
│ http://example.com/?page=1&lr=213               │ 
└─────────────────────────────────────────────────┘

decodeURLComponent

Returns the decoded URL.
Syntax

decodeURLComponent(URL)

Arguments

URL– URL string

Return value

Decoded URL string

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

domain

Extracts the hostname from the URL.
Syntax

domain(url)

Arguments

url— URL。 Type: String. URL

The scheme can be specified or not specified. Example:

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

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

some.svn-hosting.com 
some.svn-hosting.com 
yandex.com

Return value

Hostname. If ByteHouse can analyze the input string as a URL.
Empty string. If ByteHouse cannot parse the input character string as a URL.

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

domainWithoutWWW

Returns the domain name and removes at most one leading "www." (if present).
Syntax

domainWithoutWWW(url)

Parameter

url— URL。 Type: String.

Return value

Domain name. If ByteHouse can parse the input character string as a URL.
Empty string. If ByteHouse cannot parse the input character string as a URL.

Type: String.
Example

SELECT domainWithoutWWW('http://www.example.com#fragment');

┌─domainWithoutWWW('http://www.example.com#fragment')─┐ 
│ example.com                                         │ 
└─────────────────────────────────────────────────────┘

extractURLParameter

Returns the value of the "name" Param in the URL (if present). Otherwise, returns an empty string. If there are multiple Params with the same Parameter name, returns the first one. This function works under the assumption that the encoding method of the Parameter name in the URL is exactly the same as the encoding method in the provided Param.
Syntax

extractURLParameter(URL, name)

Parameter

URL– URL string
name Parameter name

Return value

Parameter value

Example

SELECT extractURLParameter('http://example.com/?page=1&lr=213','page')

┌─extractURLParameter('http://example.com/?page=1&lr=213', 'page')─┐ 
│ 1                                                                │ 
└──────────────────────────────────────────────────────────────────┘

extractURLParameterNames

Returns a string array of names corresponding to the Parameter names in the URL. The value is not decoded in any way.
Syntax

extractURLParameterNames(URL)

Parameter

URL– URL string

Return value

Parameter name list

Example

SELECT extractURLParameterNames('http://example.com/?page=1&lr=213')

┌─extractURLParameterNames('http://example.com/?page=1&lr=213')─┐ 
│ ['page', 'lr']                                                │ 
└───────────────────────────────────────────────────────────────┘

extractURLParameters

Returns a string array of name=value pairs corresponding to the Params in the URL. These values are not decoded in any way.
Syntax

extractURLParameters(URL)

Parameter

URL– URL string

Return value

Parameter list

Example

SELECT extractURLParameters('http://example.com/?page=1&lr=213')

┌─extractURLParameters('http://example.com/?page=1&lr=213')─┐ 
│ ['page=1', 'lr=213']                                      │ 
└───────────────────────────────────────────────────────────┘

firstSignificantSubdomain

Returns the "first significant subdomain". This is a non-standard concept specific to Yandex.Metrica. If the first significant subdomain is "com", "net", "org", or "co", then it is a second-level domain name. Otherwise, it is a third-level domain name.
Syntax

firstSignificantSubdomain(URL)

Parameter

URL– URL string

Return value

First significant subdomain

Example

SELECT firstSignificantSubdomain('https://www.example.com.cn/')

┌─firstSignificantSubdomain('https://www.example.com.cn/')─┐ 
│ example                                                  │ 
└──────────────────────────────────────────────────────────┘

fragment

Returns the fragment identifier. The fragment does not include the initial hash sign.
Syntax

fragment(URL)

Parameter

URL– URL string

Return value

Fragment string

Example

SELECT fragment('http://example.com#fragment')

┌─fragment('http://example.com#fragment')─┐ 
│ fragment                                │ 
└─────────────────────────────────────────┘

path

Returns the path. Example: /top/news.html The path does not include the query string.
Syntax

path(URL)

Parameter

URL– URL string

Return value

Path string

Example

SELECT path('http://example.com/top/news.html')

┌─path('http://example.com/top/news.html')─┐ 
│ /top/news.html                           │ 
└──────────────────────────────────────────┘

protocol

Extracts the protocol from the URL.
Typical return value examples: http, https, ftp, mailto, tel, magnet…
Syntax

protocol(URL)

Parameter

URL– URL string

Return value

Protocol string

Example

SELECT protocol('http://example.com')

┌─protocol('http://example.com')─┐ 
│ http                           │ 
└────────────────────────────────┘

queryString

Returns the query string. Example: page=1&lr=213. The query string does not include the initial question mark, #, or anything after the #.
Syntax

queryString(URL)

Parameter

URL– URL string

Return value

Request parameters

Example

SELECT queryString('http://example.com/?page=1&lr=213')

┌─queryString('http://example.com/?page=1&lr=213')─┐ 
│ page=1&lr=213                                    │ 
└──────────────────────────────────────────────────┘

queryStringAndFragment

Returns the query string and fragment identifier. Example: page=1#29390.
Syntax

queryStringAndFragment(URL)

Parameter

URL– URL string

Return value

Query and fragment string

Example

SELECT queryStringAndFragment('http://example.com/?page=1&lr=213#fragment')

┌─queryStringAndFragment('http://example.com/?page=1&lr=213#fragment')─┐ 
│ page=1&lr=213#fragment                                               │ 
└──────────────────────────────────────────────────────────────────────┘

topLevelDomain

Extract the top-level domain from a URL.
Syntax

topLevelDomain(url)

Param

url— URL。 Type: String. URL

The scheme can be specified or omitted. Example:

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

Return value

domain name. If ByteHouse can parse the input string as a URL.
Empty string. If ByteHouse cannot parse the input string as a URL.

Type: String.
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                                                                │ 
└────────────────────────────────────────────────────────────────────┘

Last updated: 2026.03.31 16:13:53

ByteHouse Cloud Data Warehouse Version

#

IPv4NumToString #

IPv4NumToStringClassC #

IPv4StringToNum #

IPv4ToIPv6 #

IPv6NumToString #

IPv6StringToNum #

cutIPv6 #

toIPv4 #

toIPv6 #

URLHierarchy #

URLPathHierarchy #

cutFragment #

cutQueryString #

cutQueryStringAndFragment #

cutToFirstSignificantSubdomain #

cutURLParameter #

cutWWW #

decodeURLComponent #

domain #

domainWithoutWWW #

extractURLParameter #

extractURLParameterNames #

extractURLParameters #

firstSignificantSubdomain #

fragment #

path #

protocol #

queryString #

queryStringAndFragment #

topLevelDomain #

IPv4NumToString

IPv4NumToStringClassC

IPv4StringToNum

IPv4ToIPv6

IPv6NumToString

IPv6StringToNum

cutIPv6

toIPv4

toIPv6

URLHierarchy

URLPathHierarchy

cutFragment

cutQueryString

cutQueryStringAndFragment

cutToFirstSignificantSubdomain

cutURLParameter

cutWWW

decodeURLComponent

domain

domainWithoutWWW

extractURLParameter

extractURLParameterNames

extractURLParameters

firstSignificantSubdomain

fragment

path

protocol

queryString

queryStringAndFragment

topLevelDomain