Functions for Working with Strings

Functions for searching in strings and for replacing in strings are described separately.

empty

Checks whether the input string is empty. A string is considered non-empty if it contains at least one byte, even if this byte is a space or the null byte.

The function is also available for arrays and UUIDs.

Syntax

empty(x)

Arguments

x — Input value. String.

Returned value

Returns 1 for an empty string or 0 for a non-empty string. UInt8.

Example

SELECT empty('');

Result:

┌─empty('')─┐
│         1 │
└───────────┘

notEmpty

Checks whether the input string is non-empty. A string is considered non-empty if it contains at least one byte, even if this byte is a space or the null byte.

The function is also available for arrays and UUIDs.

Syntax

notEmpty(x)

Arguments

x — Input value. String.

Returned value

Returns 1 for a non-empty string or 0 for an empty string string. UInt8.

Example

SELECT notEmpty('text');

Result:

┌─notEmpty('text')─┐
│                1 │
└──────────────────┘

length

Returns the length of a string in bytes rather than in characters or Unicode code points. The function also works for arrays.

Alias: OCTET_LENGTH

Syntax

length(s)

Parameters

s — An input string or array. String/Array.

Returned value

Length of the string or array s in bytes. UInt64.

Example

Query:

SELECT length('Hello, world!');

Result:

┌─length('Hello, world!')─┐
│                      13 │
└─────────────────────────┘

Query:

SELECT length([1, 2, 3, 4]);

Result:

┌─length([1, 2, 3, 4])─┐
│                    4 │
└──────────────────────┘

lengthUTF8

Returns the length of a string in Unicode code points rather than in bytes or characters. It assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Aliases:

CHAR_LENGTH
CHARACTER_LENGTH

Syntax

lengthUTF8(s)

Parameters

s — String containing valid UTF-8 encoded text. String.

Returned value

Length of the string s in Unicode code points. UInt64.

Example

Query:

SELECT lengthUTF8('Здравствуй, мир!');

Result:

┌─lengthUTF8('Здравствуй, мир!')─┐
│                             16 │
└────────────────────────────────┘

left

Returns a substring of string s with a specified offset starting from the left.

Syntax

left(s, offset)

Parameters

s — The string to calculate a substring from. String or FixedString.
offset — The number of bytes of the offset. (U)Int*.

Returned value

For positive offset: A substring of s with offset many bytes, starting from the left of the string.
For negative offset: A substring of s with length(s) - |offset| bytes, starting from the left of the string.
An empty string if length is 0.

Example

Query:

SELECT left('Hello', 3);

Result:

Hel

Query:

SELECT left('Hello', -3);

Result:

He

leftUTF8

Returns a substring of a UTF-8 encoded string s with a specified offset starting from the left.

Syntax

leftUTF8(s, offset)

Parameters

s — The UTF-8 encoded string to calculate a substring from. String or FixedString.
offset — The number of bytes of the offset. (U)Int*.

Returned value

For positive offset: A substring of s with offset many bytes, starting from the left of the string.
For negative offset: A substring of s with length(s) - |offset| bytes, starting from the left of the string.
An empty string if length is 0.

Example

Query:

SELECT leftUTF8('Привет', 4);

Result:

Прив

Query:

SELECT leftUTF8('Привет', -4);

Result:

Пр

leftPad

Pads a string from the left with spaces or with a specified string (multiple times, if needed) until the resulting string reaches the specified length.

Syntax

leftPad(string, length[, pad_string])

Alias: LPAD

Arguments

string — Input string that should be padded. String.
length — The length of the resulting string. UInt or Int. If the value is smaller than the input string length, then the input string is shortened to length characters.
pad_string — The string to pad the input string with. String. Optional. If not specified, then the input string is padded with spaces.

Returned value

A left-padded string of the given length. String.

Example

SELECT leftPad('abc', 7, '*'), leftPad('def', 7);

Result:

┌─leftPad('abc', 7, '*')─┬─leftPad('def', 7)─┐
│ ****abc                │     def           │
└────────────────────────┴───────────────────┘

leftPadUTF8

Pads the string from the left with spaces or a specified string (multiple times, if needed) until the resulting string reaches the given length. Unlike leftPad which measures the string length in bytes, the string length is measured in code points.

Syntax

leftPadUTF8(string, length[, pad_string])

Arguments

string — Input string that should be padded. String.
length — The length of the resulting string. UInt or Int. If the value is smaller than the input string length, then the input string is shortened to length characters.
pad_string — The string to pad the input string with. String. Optional. If not specified, then the input string is padded with spaces.

Returned value

A left-padded string of the given length. String.

Example

SELECT leftPadUTF8('абвг', 7, '*'), leftPadUTF8('дежз', 7);

Result:

┌─leftPadUTF8('абвг', 7, '*')─┬─leftPadUTF8('дежз', 7)─┐
│ ***абвг                     │    дежз                │
└─────────────────────────────┴────────────────────────┘

right

Returns a substring of string s with a specified offset starting from the right.

Syntax

right(s, offset)

Parameters

s — The string to calculate a substring from. String or FixedString.
offset — The number of bytes of the offset. (U)Int*.

Returned value

For positive offset: A substring of s with offset many bytes, starting from the right of the string.
For negative offset: A substring of s with length(s) - |offset| bytes, starting from the right of the string.
An empty string if length is 0.

Example

Query:

SELECT right('Hello', 3);

Result:

llo

Query:

SELECT right('Hello', -3);

Result:

lo

rightUTF8

Returns a substring of UTF-8 encoded string s with a specified offset starting from the right.

Syntax

rightUTF8(s, offset)

Parameters

s — The UTF-8 encoded string to calculate a substring from. String or FixedString.
offset — The number of bytes of the offset. (U)Int*.

Returned value

For positive offset: A substring of s with offset many bytes, starting from the right of the string.
For negative offset: A substring of s with length(s) - |offset| bytes, starting from the right of the string.
An empty string if length is 0.

Example

Query:

SELECT rightUTF8('Привет', 4);

Result:

ивет

Query:

SELECT rightUTF8('Привет', -4);

Result:

ет

rightPad

Pads a string from the right with spaces or with a specified string (multiple times, if needed) until the resulting string reaches the specified length.

Syntax

rightPad(string, length[, pad_string])

Alias: RPAD

Arguments

string — Input string that should be padded. String.
length — The length of the resulting string. UInt or Int. If the value is smaller than the input string length, then the input string is shortened to length characters.
pad_string — The string to pad the input string with. String. Optional. If not specified, then the input string is padded with spaces.

Returned value

A left-padded string of the given length. String.

Example

SELECT rightPad('abc', 7, '*'), rightPad('abc', 7);

Result:

┌─rightPad('abc', 7, '*')─┬─rightPad('abc', 7)─┐
│ abc****                 │ abc                │
└─────────────────────────┴────────────────────┘

rightPadUTF8

Pads the string from the right with spaces or a specified string (multiple times, if needed) until the resulting string reaches the given length. Unlike rightPad which measures the string length in bytes, the string length is measured in code points.

Syntax

rightPadUTF8(string, length[, pad_string])

Arguments

string — Input string that should be padded. String.
length — The length of the resulting string. UInt or Int. If the value is smaller than the input string length, then the input string is shortened to length characters.
pad_string — The string to pad the input string with. String. Optional. If not specified, then the input string is padded with spaces.

Returned value

A right-padded string of the given length. String.

Example

SELECT rightPadUTF8('абвг', 7, '*'), rightPadUTF8('абвг', 7);

Result:

┌─rightPadUTF8('абвг', 7, '*')─┬─rightPadUTF8('абвг', 7)─┐
│ абвг***                      │ абвг                    │
└──────────────────────────────┴─────────────────────────┘

compareSubstrings

Compare two strings lexicographically.

Syntax

compareSubstrings(string1, string2, string1_offset, string2_offset, num_bytes);

Arguments

string1 — The first string to compare. String
string2 - The second string to compare.String
string1_offset — The position (zero-based) in string1 from which the comparison starts. UInt*.
string2_offset — The position (zero-based index) in string2 from which the comparison starts. UInt*.
num_bytes — The maximum number of bytes to compare in both strings. If string_offset + num_bytes exceeds the end of an input string, num_bytes will be reduced accordingly. UInt*.

Returned value

-1 — If string1[string1_offset : string1_offset + num_bytes] < string2[string2_offset : string2_offset + num_bytes].
0 — If string1[string1_offset : string1_offset + num_bytes] = string2[string2_offset : string2_offset + num_bytes].
1 — If string1[string1_offset : string1_offset + num_bytes] > string2[string2_offset : string2_offset + num_bytes].

Example

Query:

SELECT compareSubstrings('Saxony', 'Anglo-Saxon', 0, 6, 5) AS result,

Result:

┌─result─┐
│      0 │
└────────┘

lower

Converts the ASCII Latin symbols in a string to lowercase.

Syntax*

lower(input)

Alias: lcase

Parameters

input: A string type String.

Returned value

A String data type value.

Example

Query:

SELECT lower('CLICKHOUSE');

┌─lower('CLICKHOUSE')─┐
│ clickhouse          │
└─────────────────────┘

upper

Converts the ASCII Latin symbols in a string to uppercase.

Syntax

upper(input)

Alias: ucase

Parameters

input — A string type String.

Returned value

A String data type value.

Examples

Query:

SELECT upper('clickhouse');

┌─upper('clickhouse')─┐
│ CLICKHOUSE          │
└─────────────────────┘

lowerUTF8

Converts a string to lowercase, assuming that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Note

Does not detect the language, e.g. for Turkish the result might not be exactly correct (i/İ vs. i/I). If the length of the UTF-8 byte sequence is different for upper and lower case of a code point (such as ẞ and ß), the result may be incorrect for this code point.

Syntax

lowerUTF8(input)

Parameters

input — A string type String.

Returned value

A String data type value.

Example

Query:

SELECT lowerUTF8('MÜNCHEN') AS Lowerutf8;

Result:

┌─Lowerutf8─┐
│ münchen   │
└───────────┘

upperUTF8

Converts a string to uppercase, assuming that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Note

Syntax

upperUTF8(input)

Parameters

input — A string type String.

Returned value

A String data type value.

Example

Query:

SELECT upperUTF8('München') AS Upperutf8;

Result:

┌─Upperutf8─┐
│ MÜNCHEN   │
└───────────┘

isValidUTF8

Returns 1, if the set of bytes constitutes valid UTF-8-encoded text, otherwise 0.

Syntax

isValidUTF8(input)

Parameters

input — A string type String.

Returned value

Returns 1, if the set of bytes constitutes valid UTF-8-encoded text, otherwise 0.

Query:

SELECT isValidUTF8('\xc3\xb1') AS valid, isValidUTF8('\xc3\x28') AS invalid;

Result:

┌─valid─┬─invalid─┐
│     1 │       0 │
└───────┴─────────┘

toValidUTF8

Replaces invalid UTF-8 characters by the � (U+FFFD) character. All running in a row invalid characters are collapsed into the one replacement character.

Syntax

toValidUTF8(input_string)

Arguments

input_string — Any set of bytes represented as the String data type object.

Returned value

A valid UTF-8 string.

Example

SELECT toValidUTF8('\x61\xF0\x80\x80\x80b');

┌─toValidUTF8('a����b')─┐
│ a�b                   │
└───────────────────────┘

repeat

Concatenates a string as many times with itself as specified.

Syntax

repeat(s, n)

Alias: REPEAT

Arguments

s — The string to repeat. String.
n — The number of times to repeat the string. UInt* or Int*.

Returned value

A string containing string s repeated n times. If n <= 0, the function returns the empty string. String.

Example

SELECT repeat('abc', 10);

Result:

┌─repeat('abc', 10)──────────────┐
│ abcabcabcabcabcabcabcabcabcabc │
└────────────────────────────────┘

space

Concatenates a space ( ) as many times with itself as specified.

Syntax

space(n)

Alias: SPACE.

Arguments

n — The number of times to repeat the space. UInt* or Int*.

Returned value

The string containing string repeated n times. If n <= 0, the function returns the empty string. String.

Example

Query:

SELECT space(3);

Result:

┌─space(3) ────┐
│              │
└──────────────┘

reverse

Reverses the sequence of bytes in a string.

reverseUTF8

Reverses a sequence of Unicode code points in a string. Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

concat

Concatenates the given arguments.

Syntax

concat(s1, s2, ...)

Arguments

Values of arbitrary type.

Arguments which are not of types String or FixedString are converted to strings using their default serialization. As this decreases performance, it is not recommended to use non-String/FixedString arguments.

Returned values

The String created by concatenating the arguments.

If any of arguments is NULL, the function returns NULL.

Example

Query:

SELECT concat('Hello, ', 'World!');

Result:

┌─concat('Hello, ', 'World!')─┐
│ Hello, World!               │
└─────────────────────────────┘

Query:

SELECT concat(42, 144);

Result:

┌─concat(42, 144)─┐
│ 42144           │
└─────────────────┘

Note

|| operator

Use the || operator for string concatenation as a concise alternative to concat(). For example, 'Hello, ' || 'World!' is equivalent to concat('Hello, ', 'World!').

concatAssumeInjective

Like concat but assumes that concat(s1, s2, ...) → sn is injective. Can be used for optimization of GROUP BY.

A function is called injective if it returns for different arguments different results. In other words: different arguments never produce identical result.

Syntax

concatAssumeInjective(s1, s2, ...)

Arguments

Values of type String or FixedString.

Returned values

The String created by concatenating the arguments.

If any of argument values is NULL, the function returns NULL.

Example

Input table:

CREATE TABLE key_val(`key1` String, `key2` String, `value` UInt32) ENGINE = TinyLog;
INSERT INTO key_val VALUES ('Hello, ','World',1), ('Hello, ','World',2), ('Hello, ','World!',3), ('Hello',', World!',2);
SELECT * from key_val;

┌─key1────┬─key2─────┬─value─┐
│ Hello,  │ World    │     1 │
│ Hello,  │ World    │     2 │
│ Hello,  │ World!   │     3 │
│ Hello   │ , World! │     2 │
└─────────┴──────────┴───────┘

SELECT concat(key1, key2), sum(value) FROM key_val GROUP BY concatAssumeInjective(key1, key2);

Result:

┌─concat(key1, key2)─┬─sum(value)─┐
│ Hello, World!      │          3 │
│ Hello, World!      │          2 │
│ Hello, World       │          3 │
└────────────────────┴────────────┘

concatWithSeparator

Concatenates the given strings with a given separator.

Syntax

concatWithSeparator(sep, expr1, expr2, expr3...)

Alias: concat_ws

Arguments

sep — separator. Const String or FixedString.
exprN — expression to be concatenated. Arguments which are not of types String or FixedString are converted to strings using their default serialization. As this decreases performance, it is not recommended to use non-String/FixedString arguments.

Returned values

The String created by concatenating the arguments.

If any of the argument values is NULL, the function returns NULL.

Example

SELECT concatWithSeparator('a', '1', '2', '3', '4')

Result:

┌─concatWithSeparator('a', '1', '2', '3', '4')─┐
│ 1a2a3a4                                      │
└──────────────────────────────────────────────┘

concatWithSeparatorAssumeInjective

Like concatWithSeparator but assumes that concatWithSeparator(sep, expr1, expr2, expr3...) → result is injective. Can be used for optimization of GROUP BY.

A function is called injective if it returns for different arguments different results. In other words: different arguments never produce identical result.

substring

Returns the substring of a string s which starts at the specified byte index offset. Byte counting starts from 1. If offset is 0, an empty string is returned. If offset is negative, the substring starts pos characters from the end of the string, rather than from the beginning. An optional argument length specifies the maximum number of bytes the returned substring may have.

Syntax

substring(s, offset[, length])

Aliases:

substr
mid
byteSlice

Arguments

s — The string to calculate a substring from. String, FixedString or Enum
offset — The starting position of the substring in s . (U)Int*.
length — The maximum length of the substring. (U)Int*. Optional.

Returned value

A substring of s with length many bytes, starting at index offset. String.

Example

SELECT 'database' AS db, substr(db, 5), substr(db, 5, 1)

Result:

┌─db───────┬─substring('database', 5)─┬─substring('database', 5, 1)─┐
│ database │ base                     │ b                           │
└──────────┴──────────────────────────┴─────────────────────────────┘

substringUTF8

Returns the substring of a string s which starts at the specified byte index offset for Unicode code points. Byte counting starts from 1. If offset is 0, an empty string is returned. If offset is negative, the substring starts pos characters from the end of the string, rather than from the beginning. An optional argument length specifies the maximum number of bytes the returned substring may have.

Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Syntax

substringUTF8(s, offset[, length])

Arguments

s — The string to calculate a substring from. String, FixedString or Enum
offset — The starting position of the substring in s . (U)Int*.
length — The maximum length of the substring. (U)Int*. Optional.

Returned value

A substring of s with length many bytes, starting at index offset.

Implementation details

Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Example

SELECT 'Täglich grüßt das Murmeltier.' AS str,
       substringUTF8(str, 9),
       substringUTF8(str, 9, 5)

Täglich grüßt das Murmeltier.    grüßt das Murmeltier.    grüßt

substringIndex

Returns the substring of s before count occurrences of the delimiter delim, as in Spark or MySQL.

Syntax

substringIndex(s, delim, count)

Alias: SUBSTRING_INDEX Arguments

s — The string to extract substring from. String.
delim — The character to split. String.
count — The number of occurrences of the delimiter to count before extracting the substring. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. UInt or Int

Example

SELECT substringIndex('www.clickhouse.com', '.', 2)

Result:

┌─substringIndex('www.clickhouse.com', '.', 2)─┐
│ www.clickhouse                               │
└──────────────────────────────────────────────┘

substringIndexUTF8

Returns the substring of s before count occurrences of the delimiter delim, specifically for Unicode code points.

Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Syntax

substringIndexUTF8(s, delim, count)

Arguments

s — The string to extract substring from. String.
delim — The character to split. String.
count — The number of occurrences of the delimiter to count before extracting the substring. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. UInt or Int

Returned value

A substring String of s before count occurrences of delim.

Implementation details

Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Example

SELECT substringIndexUTF8('www.straßen-in-europa.de', '.', 2)

www.straßen-in-europa

appendTrailingCharIfAbsent

Appends character c to string s if s is non-empty and does not end with character c.

Syntax

appendTrailingCharIfAbsent(s, c)

convertCharset

Returns string s converted from the encoding from to encoding to.

Syntax

convertCharset(s, from, to)

base32Encode

Encodes a string using Base32.

Syntax

base32Encode(plaintext)

Arguments

plaintext — String column or constant.

Returned value

A string containing the encoded value of the argument. String or FixedString.

Example

SELECT base32Encode('Encoded');

Result:

┌─base32Encode('Encoded')─┐
│ IVXGG33EMVSA====        │
└─────────────────────────┘

base32Decode

Accepts a string and decodes it using Base32 encoding scheme.

Syntax

base32Decode(encoded)

Arguments

encoded — String or FixedString. If the string is not a valid Base32-encoded value, an exception is thrown.

Returned value

A string containing the decoded value of the argument. String.

Example

SELECT base32Decode('IVXGG33EMVSA====');

Result:

┌─base32Decode('IVXGG33EMVSA====')─┐
│ Encoded                          │
└──────────────────────────────────┘

tryBase32Decode

Like base32Decode but returns an empty string in case of error.

Syntax

tryBase32Decode(encoded)

Parameters

encoded: String or FixedString. If the string is not a valid Base32-encoded value, returns an empty string in case of error.

Returned value

A string containing the decoded value of the argument.

Examples

Query:

SELECT tryBase32Decode('IVXGG33EMVSA====') AS res, tryBase32Decode('invalid') AS res_invalid;

┌─res─────┬─res_invalid─┐
│ Encoded │             │
└─────────┴─────────────┘

base58Encode

Encodes a string using Base58 in the "Bitcoin" alphabet.

Syntax

base58Encode(plaintext)

Arguments

plaintext — String column or constant.

Returned value

A string containing the encoded value of the argument. String or FixedString.

Example

SELECT base58Encode('Encoded');

Result:

┌─base58Encode('Encoded')─┐
│ 3dc8KtHrwM              │
└─────────────────────────┘

base58Decode

Accepts a string and decodes it using Base58 encoding scheme using "Bitcoin" alphabet.

Syntax

base58Decode(encoded)

Arguments

encoded — String or FixedString. If the string is not a valid Base58-encoded value, an exception is thrown.

Returned value

A string containing the decoded value of the argument. String.

Example

SELECT base58Decode('3dc8KtHrwM');

Result:

┌─base58Decode('3dc8KtHrwM')─┐
│ Encoded                    │
└────────────────────────────┘

tryBase58Decode

Like base58Decode but returns an empty string in case of error.

Syntax

tryBase58Decode(encoded)

Parameters

encoded: String or FixedString. If the string is not a valid Base58-encoded value, returns an empty string in case of error.

Returned value

A string containing the decoded value of the argument.

Examples

Query:

SELECT tryBase58Decode('3dc8KtHrwM') AS res, tryBase58Decode('invalid') AS res_invalid;

┌─res─────┬─res_invalid─┐
│ Encoded │             │
└─────────┴─────────────┘

base64Encode

Encodes a String or FixedString as base64, according to RFC 4648.

Alias: TO_BASE64.

Syntax

base64Encode(plaintext)

Arguments

plaintext — String column or constant.

Returned value

A string containing the encoded value of the argument.

Example

SELECT base64Encode('clickhouse');

Result:

┌─base64Encode('clickhouse')─┐
│ Y2xpY2tob3VzZQ==           │
└────────────────────────────┘

base64URLEncode

Encodes an URL (String or FixedString) as base64 with URL-specific modifications, according to RFC 4648.

Syntax

base64URLEncode(url)

Arguments

url — String column or constant.

Returned value

A string containing the encoded value of the argument.

Example

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

Result:

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

base64Decode

Accepts a String and decodes it from base64, according to RFC 4648. Throws an exception in case of an error.

Alias: FROM_BASE64.

Syntax

base64Decode(encoded)

Arguments

encoded — String column or constant. If the string is not a valid Base64-encoded value, an exception is thrown.

Returned value

A string containing the decoded value of the argument.

Example

SELECT base64Decode('Y2xpY2tob3VzZQ==');

Result:

┌─base64Decode('Y2xpY2tob3VzZQ==')─┐
│ clickhouse                       │
└──────────────────────────────────┘

base64URLDecode

Accepts a base64-encoded URL and decodes it from base64 with URL-specific modifications, according to RFC 4648. Throws an exception in case of an error.

Syntax

base64URLDecode(encodedUrl)

Arguments

encodedURL — String column or constant. If the string is not a valid Base64-encoded value with URL-specific modifications, an exception is thrown.

Returned value

A string containing the decoded value of the argument.

Example

SELECT base64URLDecode('aHR0cDovL2NsaWNraG91c2UuY29t');

Result:

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

tryBase64Decode

Like base64Decode but returns an empty string in case of error.

Syntax

tryBase64Decode(encoded)

Arguments

encoded — String column or constant. If the string is not a valid Base64-encoded value, returns an empty string.

Returned value

A string containing the decoded value of the argument.

Examples

Query:

SELECT tryBase64Decode('RW5jb2RlZA==') AS res, tryBase64Decode('invalid') AS res_invalid;

┌─res────────┬─res_invalid─┐
│ clickhouse │             │
└────────────┴─────────────┘

tryBase64URLDecode

Like base64URLDecode but returns an empty string in case of error.

Syntax

tryBase64URLDecode(encodedUrl)

Parameters

encodedURL — String column or constant. If the string is not a valid Base64-encoded value with URL-specific modifications, returns an empty string.

Returned value

A string containing the decoded value of the argument.

Examples

Query:

SELECT tryBase64URLDecode('aHR0cDovL2NsaWNraG91c2UuY29t') AS res, tryBase64Decode('aHR0cHM6Ly9jbGlja') AS res_invalid;

┌─res────────────────────┬─res_invalid─┐
│ https://clickhouse.com │             │
└────────────────────────┴─────────────┘

endsWith

Returns whether string str ends with suffix.

Syntax

endsWith(str, suffix)

endsWithUTF8

Returns whether string str ends with suffix, the difference between endsWithUTF8 and endsWith is that endsWithUTF8 match str and suffix by UTF-8 characters.

Syntax

endsWithUTF8(str, suffix)

Example

SELECT endsWithUTF8('中国', '\xbd'), endsWith('中国', '\xbd')

Result:

┌─endsWithUTF8('中国', '½')─┬─endsWith('中国', '½')─┐
│                        0 │                    1 │
└──────────────────────────┴──────────────────────┘

startsWith

Returns whether string str starts with prefix.

Syntax

startsWith(str, prefix)

Example

SELECT startsWith('Spider-Man', 'Spi');

startsWithUTF8

Available in version 23.8 and later

Returns whether string str starts with prefix, the difference between startsWithUTF8 and startsWith is that startsWithUTF8 match str and suffix by UTF-8 characters. Example

SELECT startsWithUTF8('中国', '\xe4'), startsWith('中国', '\xe4')

Result:

┌─startsWithUTF8('中国', '⥩─┬─startsWith('中国', '⥩─┐
│                          0 │                      1 │
└────────────────────────────┴────────────────────────┘

trim

Removes the specified characters from the start or end of a string. If not specified otherwise, the function removes whitespace (ASCII-character 32).

Syntax

trim([[LEADING|TRAILING|BOTH] trim_character FROM] input_string)

Arguments

trim_character — The characters to trim. String.
input_string — String for trim. String.

Returned value

A string without leading and/or trailing specified characters. String.

Example

SELECT trim(BOTH ' ()' FROM '(   Hello, world!   )');

Result:

┌─trim(BOTH ' ()' FROM '(   Hello, world!   )')─┐
│ Hello, world!                                 │
└───────────────────────────────────────────────┘

trimLeft

Removes the consecutive occurrences of whitespace (ASCII-character 32) from the start of a string.

Syntax

trimLeft(input_string[, trim_characters])

Alias: ltrim.

Arguments

input_string — The string to trim. String.
trim_characters — The characters to trim. Optional. String. If not specified, ' ' ( single whitespace) is used as trim character.

Returned value

A string without leading common whitespaces. String.

Example

SELECT trimLeft('     Hello, world!     ');

Result:

┌─trimLeft('     Hello, world!     ')─┐
│ Hello, world!                       │
└─────────────────────────────────────┘

trimRight

Removes the consecutive occurrences of whitespace (ASCII-character 32) from the end of a string.

Syntax

trimRight(input_string[, trim_characters])

Alias: rtrim.

Arguments

input_string — The string to trim. String.
trim_characters — The characters to trim. Optional. String. If not specified, ' ' ( single whitespace) is used as trim character.

Returned value

A string without trailing common whitespaces. String.

Example

SELECT trimRight('     Hello, world!     ');

Result:

┌─trimRight('     Hello, world!     ')─┐
│      Hello, world!                   │
└──────────────────────────────────────┘

trimBoth

Removes the consecutive occurrences of whitespace (ASCII-character 32) from both ends of a string.

Syntax

trimBoth(input_string[, trim_characters])

Alias: trim.

Arguments

input_string — The string to trim. String.
trim_characters — The characters to trim. Optional. String. If not specified, ' ' ( single whitespace) is used as trim character.

Returned value

A string without leading and trailing common whitespaces. String.

Example

SELECT trimBoth('     Hello, world!     ');

Result:

┌─trimBoth('     Hello, world!     ')─┐
│ Hello, world!                       │
└─────────────────────────────────────┘

CRC32

Returns the CRC32 checksum of a string using CRC-32-IEEE 802.3 polynomial and initial value 0xffffffff (zlib implementation).

The result type is UInt32.

CRC32IEEE

Returns the CRC32 checksum of a string, using CRC-32-IEEE 802.3 polynomial.

The result type is UInt32.

CRC64

Returns the CRC64 checksum of a string, using CRC-64-ECMA polynomial.

The result type is UInt64.

normalizeUTF8NFC

Converts a string to NFC normalized form, assuming the string is valid UTF8-encoded text.

Syntax

normalizeUTF8NFC(words)

Arguments

words — UTF8-encoded input string. String.

Returned value

String transformed to NFC normalization form. String.

Example

SELECT length('â'), normalizeUTF8NFC('â') AS nfc, length(nfc) AS nfc_len;

Result:

┌─length('â')─┬─nfc─┬─nfc_len─┐
│           2 │ â   │       2 │
└─────────────┴─────┴─────────┘

normalizeUTF8NFD

Converts a string to NFD normalized form, assuming the string is valid UTF8-encoded text.

Syntax

normalizeUTF8NFD(words)

Arguments

words — UTF8-encoded input string. String.

Returned value

String transformed to NFD normalization form. String.

Example

SELECT length('â'), normalizeUTF8NFD('â') AS nfd, length(nfd) AS nfd_len;

Result:

┌─length('â')─┬─nfd─┬─nfd_len─┐
│           2 │ â   │       3 │
└─────────────┴─────┴─────────┘

normalizeUTF8NFKC

Converts a string to NFKC normalized form, assuming the string is valid UTF8-encoded text.

Syntax

normalizeUTF8NFKC(words)

Arguments

words — UTF8-encoded input string. String.

Returned value

String transformed to NFKC normalization form. String.

Example

SELECT length('â'), normalizeUTF8NFKC('â') AS nfkc, length(nfkc) AS nfkc_len;

Result:

┌─length('â')─┬─nfkc─┬─nfkc_len─┐
│           2 │ â    │        2 │
└─────────────┴──────┴──────────┘

normalizeUTF8NFKD

Converts a string to NFKD normalized form, assuming the string is valid UTF8-encoded text.

Syntax

normalizeUTF8NFKD(words)

Arguments

words — UTF8-encoded input string. String.

Returned value

String transformed to NFKD normalization form. String.

Example

SELECT length('â'), normalizeUTF8NFKD('â') AS nfkd, length(nfkd) AS nfkd_len;

Result:

┌─length('â')─┬─nfkd─┬─nfkd_len─┐
│           2 │ â    │        3 │
└─────────────┴──────┴──────────┘

encodeXMLComponent

Escapes characters with special meaning in XML such that they can afterwards be place into a XML text node or attribute.

The following characters are replaced: <, &, >, ", '. Also see the list of XML and HTML character entity references.

Syntax

encodeXMLComponent(x)

Arguments

x — An input string. String.

Returned value

The escaped string. String.

Example

SELECT encodeXMLComponent('Hello, "world"!');
SELECT encodeXMLComponent('<123>');
SELECT encodeXMLComponent('&clickhouse');
SELECT encodeXMLComponent('\'foo\'');

Result:

Hello, &quot;world&quot;!
&lt;123&gt;
&amp;clickhouse
&apos;foo&apos;

decodeXMLComponent

Un-escapes substrings with special meaning in XML. These substrings are: " & ' > <

This function also replaces numeric character references with Unicode characters. Both decimal (like ✓) and hexadecimal (✓) forms are supported.

Syntax

decodeXMLComponent(x)

Arguments

x — An input string. String.

Returned value

The un-escaped string. String.

Example

SELECT decodeXMLComponent('&apos;foo&apos;');
SELECT decodeXMLComponent('&lt; &#x3A3; &gt;');

Result:

'foo'
< Σ >

decodeHTMLComponent

Un-escapes substrings with special meaning in HTML. For example: &hbar; > &diamondsuit; &heartsuit; < etc.

This function also replaces numeric character references with Unicode characters. Both decimal (like ✓) and hexadecimal (✓) forms are supported.

Syntax

decodeHTMLComponent(x)

Arguments

x — An input string. String.

Returned value

The un-escaped string. String.

Example

SELECT decodeHTMLComponent(''CH');
SELECT decodeHTMLComponent('I&heartsuit;ClickHouse');

Result:

'CH'
I♥ClickHouse'

extractTextFromHTML

This function extracts plain text from HTML or XHTML.

It does not conform 100% to the HTML, XML or XHTML specification but the implementation is reasonably accurate and fast. The rules are the following:

Comments are skipped. Example: . Comment must end with -->. Nested comments are disallowed. Note: constructions like  are not valid comments in HTML but they are skipped by other rules.
CDATA is pasted verbatim. Note: CDATA is XML/XHTML-specific and processed on a "best-effort" basis.
script and style elements are removed with all their content. Note: it is assumed that closing tag cannot appear inside content. For example, in JS string literal has to be escaped like "<\/script>". Note: comments and CDATA are possible inside script or style - then closing tags are not searched inside CDATA. Example: <script><![CDATA[</script>]]></script>. But they are still searched inside comments. Sometimes it becomes complicated: <script>var x = ""; alert(x + y);</script> Note: script and style can be the names of XML namespaces - then they are not treated like usual script or style elements. Example: <script:a>Hello</script:a>. Note: whitespaces are possible after closing tag name: </script > but not before: < / script>.
Other tags or tag-like elements are skipped without inner content. Example: <a>.</a> Note: it is expected that this HTML is illegal: <a test=">"></a> Note: it also skips something like tags: <>, <!>, etc. Note: tag without end is skipped to the end of input: <hello
HTML and XML entities are not decoded. They must be processed by separate function.
Whitespaces in the text are collapsed or inserted by specific rules.
- Whitespaces at the beginning and at the end are removed.
- Consecutive whitespaces are collapsed.
- But if the text is separated by other elements and there is no whitespace, it is inserted.
- It may cause unnatural examples: Hello<b>world</b>, Helloworld - there is no whitespace in HTML, but the function inserts it. Also consider: Hello<p>world</p>, Hello<br>world. This behavior is reasonable for data analysis, e.g. to convert HTML to a bag of words.
Also note that correct handling of whitespaces requires the support of <pre></pre> and CSS display and white-space properties.

Syntax

extractTextFromHTML(x)

Arguments

x — input text. String.

Returned value

Extracted text. String.

Example

The first example contains several tags and a comment and also shows whitespace processing. The second example shows CDATA and script tag processing. In the third example text is extracted from the full HTML response received by the url function.

SELECT extractTextFromHTML(' <p> A text <i>with</i><b>tags</b>. <!-- comments --> </p> ');
SELECT extractTextFromHTML('<![CDATA[The content within <b>CDATA</b>]]> <script>alert("Script");</script>');
SELECT extractTextFromHTML(html) FROM url('http://www.donothingfor2minutes.com/', RawBLOB, 'html String');

Result:

A text with tags .
The content within <b>CDATA</b>
Do Nothing for 2 Minutes 2:00 &nbsp;

ascii

Returns the ASCII code point (as Int32) of the first character of string s.

If s is empty, the result is 0. If the first character is not an ASCII character or not part of the Latin-1 supplement range of UTF-16, the result is undefined.

Syntax

ascii(s)

soundex

Returns the Soundex code of a string.

Syntax

soundex(val)

Arguments

val — Input value. String

Returned value

The Soundex code of the input value. String

Example

SELECT soundex('aksel');

Result:

┌─soundex('aksel')─┐
│ A240             │
└──────────────────┘

punycodeEncode

Returns the Punycode representation of a string. The string must be UTF8-encoded, otherwise the behavior is undefined.

Syntax

punycodeEncode(val)

Arguments

val — Input value. String

Returned value

A Punycode representation of the input value. String

Example

SELECT punycodeEncode('München');

Result:

┌─punycodeEncode('München')─┐
│ Mnchen-3ya                │
└───────────────────────────┘

punycodeDecode

Returns the UTF8-encoded plaintext of a Punycode-encoded string. If no valid Punycode-encoded string is given, an exception is thrown.

Syntax

punycodeEncode(val)

Arguments

val — Punycode-encoded string. String

Returned value

The plaintext of the input value. String

Example

SELECT punycodeDecode('Mnchen-3ya');

Result:

┌─punycodeDecode('Mnchen-3ya')─┐
│ München                      │
└──────────────────────────────┘

tryPunycodeDecode

Like punycodeDecode but returns an empty string if no valid Punycode-encoded string is given.

idnaEncode

Returns the ASCII representation (ToASCII algorithm) of a domain name according to the Internationalized Domain Names in Applications (IDNA) mechanism. The input string must be UTF-encoded and translatable to an ASCII string, otherwise an exception is thrown. Note: No percent decoding or trimming of tabs, spaces or control characters is performed.

Syntax

idnaEncode(val)

Arguments

val — Input value. String

Returned value

A ASCII representation according to the IDNA mechanism of the input value. String

Example

SELECT idnaEncode('straße.münchen.de');

Result:

┌─idnaEncode('straße.münchen.de')─────┐
│ xn--strae-oqa.xn--mnchen-3ya.de     │
└─────────────────────────────────────┘

tryIdnaEncode

Like idnaEncode but returns an empty string in case of an error instead of throwing an exception.

idnaDecode

Returns the Unicode (UTF-8) representation (ToUnicode algorithm) of a domain name according to the Internationalized Domain Names in Applications (IDNA) mechanism. In case of an error (e.g. because the input is invalid), the input string is returned. Note that repeated application of idnaEncode() and idnaDecode() does not necessarily return the original string due to case normalization.

Syntax

idnaDecode(val)

Arguments

val — Input value. String

Returned value

A Unicode (UTF-8) representation according to the IDNA mechanism of the input value. String

Example

SELECT idnaDecode('xn--strae-oqa.xn--mnchen-3ya.de');

Result:

┌─idnaDecode('xn--strae-oqa.xn--mnchen-3ya.de')─┐
│ straße.münchen.de                             │
└───────────────────────────────────────────────┘

byteHammingDistance

Calculates the hamming distance between two byte strings.

Syntax

byteHammingDistance(string1, string2)

Examples

SELECT byteHammingDistance('karolin', 'kathrin');

Result:

┌─byteHammingDistance('karolin', 'kathrin')─┐
│                                         3 │
└───────────────────────────────────────────┘

Alias: mismatches

stringJaccardIndex

Calculates the Jaccard similarity index between two byte strings.

Syntax

stringJaccardIndex(string1, string2)

Examples

SELECT stringJaccardIndex('clickhouse', 'mouse');

Result:

┌─stringJaccardIndex('clickhouse', 'mouse')─┐
│                                       0.4 │
└───────────────────────────────────────────┘

stringJaccardIndexUTF8

Like stringJaccardIndex but for UTF8-encoded strings.

editDistance

Calculates the edit distance between two byte strings.

Syntax

editDistance(string1, string2)

Examples

SELECT editDistance('clickhouse', 'mouse');

Result:

┌─editDistance('clickhouse', 'mouse')─┐
│                                   6 │
└─────────────────────────────────────┘

Alias: levenshteinDistance

editDistanceUTF8

Calculates the edit distance between two UTF8 strings.

Syntax

editDistanceUTF8(string1, string2)

Examples

SELECT editDistanceUTF8('我是谁', '我是我');

Result:

┌─editDistanceUTF8('我是谁', '我是我')──┐
│                                   1 │
└─────────────────────────────────────┘

Alias: levenshteinDistanceUTF8

damerauLevenshteinDistance

Calculates the Damerau-Levenshtein distance between two byte strings.

Syntax

damerauLevenshteinDistance(string1, string2)

Examples

SELECT damerauLevenshteinDistance('clickhouse', 'mouse');

Result:

┌─damerauLevenshteinDistance('clickhouse', 'mouse')─┐
│                                                 6 │
└───────────────────────────────────────────────────┘

jaroSimilarity

Calculates the Jaro similarity between two byte strings.

Syntax

jaroSimilarity(string1, string2)

Examples

SELECT jaroSimilarity('clickhouse', 'click');

Result:

┌─jaroSimilarity('clickhouse', 'click')─┐
│                    0.8333333333333333 │
└───────────────────────────────────────┘

jaroWinklerSimilarity

Calculates the Jaro-Winkler similarity between two byte strings.

Syntax

jaroWinklerSimilarity(string1, string2)

Examples

SELECT jaroWinklerSimilarity('clickhouse', 'click');

Result:

┌─jaroWinklerSimilarity('clickhouse', 'click')─┐
│                           0.8999999999999999 │
└──────────────────────────────────────────────┘

initcap

Convert the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.

Note

Because initCap converts only the first letter of each word to upper case you may observe unexpected behaviour for words containing apostrophes or capital letters. For example:

SELECT initCap('mother''s daughter'), initCap('joe McAdam');

will return

┌─initCap('mother\'s daughter')─┬─initCap('joe McAdam')─┐
│ Mother'S Daughter             │ Joe Mcadam            │
└───────────────────────────────┴───────────────────────┘

This is a known behaviour, with no plans currently to fix it.

Syntax

initcap(val)

Arguments

val — Input value. String.

Returned value

val with the first letter of each word converted to upper case. String.

Example

Query:

SELECT initcap('building for fast');

Result:

┌─initcap('building for fast')─┐
│ Building For Fast            │
└──────────────────────────────┘

initcapUTF8

Like initcap, initcapUTF8 converts the first letter of each word to upper case and the rest to lower case. Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Note

This function does not detect the language, e.g. for Turkish the result might not be exactly correct (i/İ vs. i/I). If the length of the UTF-8 byte sequence is different for upper and lower case of a code point, the result may be incorrect for this code point.

Syntax

initcapUTF8(val)

Arguments

val — Input value. String.

Returned value

val with the first letter of each word converted to upper case. String.

Example

Query:

SELECT initcapUTF8('не тормозит');

Result:

┌─initcapUTF8('не тормозит')─┐
│ Не Тормозит                │
└────────────────────────────┘

firstLine

Returns the first line from a multi-line string.

Syntax

firstLine(val)

Arguments

val — Input value. String

Returned value

The first line of the input value or the whole value if there is no line separators. String

Example

SELECT firstLine('foo\nbar\nbaz');

Result:

┌─firstLine('foo\nbar\nbaz')─┐
│ foo                        │
└────────────────────────────┘

stringCompare

Compare two strings lexicographically.

Syntax

stringCompare(string1, string2[, str1_off, string2_offset, num_bytes]);

Arguments

string1 — The first string to compare. String
string2 - The second string to compare.String
string1_offset — The position (zero-based) in string1 from which the comparison starts. Optional, positive number.
string2_offset — The position (zero-based index) in string2 from which the comparison starts. Optional, positive number.
num_bytes — The maximum number of bytes to compare in both strings. If string_offset + num_bytes exceeds the end of an input string, num_bytes will be reduced accordingly.

Returned value

-1 — If string1[string1_offset: string1_offset + num_bytes] < string2[string2_offset:string2_offset + num_bytes] and string1_offset < len(string1) and string2_offset < len(string2). If string1_offset >= len(string1) and string2_offset < len(string2).
0 — If string1[string1_offset: string1_offset + num_bytes] = string2[string2_offset:string2_offset + num_bytes] and string1_offset < len(string1) and string2_offset < len(string2). If string1_offset >= len(string1) and string2_offset >= len(string2).
1 — If string1[string1_offset: string1_offset + num_bytes] > string2[string2_offset:string2_offset + num_bytes] and string1_offset < len(string1) and string2_offset < len(string2). If string1_offset < len(string1) and string2_offset >= len(string2).

Example

SELECT
    stringCompare('alice', 'bob', 0, 0, 3) AS result1,
    stringCompare('alice', 'alicia', 0, 0, 3) AS result2,
    stringCompare('bob', 'alice', 0, 0, 3) AS result3

Result:

   ┌─result1─┬─result2─┬─result3─┐
1. │      -1 │       0 │       1 │
   └─────────┴─────────┴─────────┘

SELECT
    stringCompare('alice', 'alicia') AS result2,
    stringCompare('alice', 'alice') AS result1,
    stringCompare('bob', 'alice') AS result3

Result:

   ┌─result2─┬─result1─┬─result3─┐
1. │      -1 │       0 │       1 │
   └─────────┴─────────┴─────────┘

sparseGrams

Finds all substrings of a given string that have a length of at least n, where the hashes of the (n-1)-grams at the borders of the substring are strictly greater than those of any (n-1)-gram inside the substring. Uses crc32 as a hash function.

Syntax

sparseGrams(s[, min_ngram_length]);

Arguments

s — An input string. String
min_ngram_length — The minimum length of extracted ngram. The default and minimal value is 3.
max_ngram_length — The maximum length of extracted ngram. The default value is 100. Should be not less than 'min_ngram_length'

Returned value

An array of selected substrings. Array(String).

Example

SELECT sparseGrams('alice', 3) AS result

Result:

   ┌─result─────────────────────┐
1. │ ['ali','lic','lice','ice'] │
   └────────────────────────────┘

sparseGramsUTF8

Syntax

sparseGramsUTF8(s[, min_ngram_length]);

Arguments

s — An input string. String
min_ngram_length — The minimum length of extracted ngram. The default and minimal value is 3.
max_ngram_length — The maximum length of extracted ngram. The default value is 100. Should be not less than 'min_ngram_length'

Returned value

An array of selected substrings. Array(String).

Example

SELECT sparseGramsUTF8('алиса', 3) AS result

Result:

   ┌─result──────────────┐
1. │ ['али','лис','иса'] │
   └─────────────────────┘

sparseGramsHashes

Finds hashes of all substrings of a given string that have a length of at least n, where the hashes of the (n-1)-grams at the borders of the substring are strictly greater than those of any (n-1)-gram inside the substring. Uses crc32 as a hash function.

Syntax

sparseGramsHashes(s[, min_ngram_length]);

Arguments

s — An input string. String
min_ngram_length — The minimum length of extracted ngram. The default and minimal value is 3.
max_ngram_length — The maximum length of extracted ngram. The default value is 100. Should be not less than 'min_ngram_length'

Returned value

An array of selected substrings crc32-c hashes. Array(UInt32).

Example

SELECT sparseGramsHashes('alice', 3) AS result

Result:

   ┌─result────────────────────────────────────────┐
1. │ [1265796434,3725069146,1689963195,3410985998] │
   └───────────────────────────────────────────────┘

sparseGramsHashesUTF8

Syntax

sparseGramsUTF8(s[, min_ngram_length]);

Arguments

s — An input string. String
min_ngram_length — The minimum length of extracted ngram. The default and minimal value is 3.
max_ngram_length — The maximum length of extracted ngram. The default value is 100. Should be not less than 'min_ngram_length'

Returned value

An array of selected substrings crc32-c hashes. Array(UInt32).

Example

SELECT sparseGramsHashesUTF8('алиса', 3) AS result

Result:

   ┌─result───────────────────────────┐
1. │ [417784657,728683856,3071092609] │
   └──────────────────────────────────┘

stringBytesUniq

Counts the number of distinct bytes in a string.

Syntax

stringBytesUniq(s)

Arguments

s — The string to analyze. String.

Returned value

The number of distinct bytes in the string. UInt16.

Example

SELECT stringBytesUniq('Hello');

Result:

┌─stringBytesUniq('Hello')─┐
│                        4 │
└──────────────────────────┘

stringBytesEntropy

Calculates Shannon's entropy of byte distribution in a string.

Syntax

stringBytesEntropy(s)

Arguments

s — The string to analyze. String.

Returned value

Shannon's entropy of byte distribution in the string. Float64.

Example

SELECT stringBytesEntropy('Hello, world!');

Result:

┌─stringBytesEntropy('Hello, world!')─┐
│                         3.07049960  │
└─────────────────────────────────────┘

conv

Converts numbers between different number bases, supporting bases from 2 to 36. This function is compatible with MySQL's CONV() function and handles partial invalid inputs by processing valid digits until the first invalid character.

Syntax

conv(number, from_base, to_base)

Arguments

number — The number to convert. String, FixedString or Integer
from_base — The source base (2-36). Integer
to_base — The target base (2-36). Integer

Returned value

Returns the string representation of the number in the target base. String

Example

SELECT conv('10', 10, 2);

Result:

┌─conv('10', 10, 2)─┐
│ 1010              │
└───────────────────┘

CRC32

Introduced in: v20.1

Calculates the CRC32 checksum of a string using the CRC-32-IEEE 802.3 polynomial and initial value 0xffffffff (zlib implementation).

Syntax

CRC32(s)

Arguments

s — String to calculate CRC32 for. String

Returned value

Returns the CRC32 checksum of the string. UInt32

Examples

Usage example

SELECT CRC32('ClickHouse')

┌─CRC32('ClickHouse')─┐
│          1538217360 │
└─────────────────────┘

CRC32IEEE

Introduced in: v20.1

Calculates the CRC32 checksum of a string using the CRC-32-IEEE 802.3 polynomial.

Syntax

CRC32IEEE(s)

Arguments

s — String to calculate CRC32 for. String

Returned value

Returns the CRC32 checksum of the string. UInt32

Examples

Usage example

SELECT CRC32IEEE('ClickHouse');

┌─CRC32IEEE('ClickHouse')─┐
│              3089448422 │
└─────────────────────────┘

CRC64

Introduced in: v20.1

Calculates the CRC64 checksum of a string using the CRC-64-ECMA polynomial.

Syntax

CRC64(s)

Arguments

s — String to calculate CRC64 for. String

Returned value

Returns the CRC64 checksum of the string. UInt64

Examples

Usage example

SELECT CRC64('ClickHouse');

┌──CRC64('ClickHouse')─┐
│ 12126588151325169346 │
└──────────────────────┘

appendTrailingCharIfAbsent

Introduced in: v1.1

Appends character c to string s if s is non-empty and does not end with character c.

Syntax

appendTrailingCharIfAbsent(s, c)

Arguments

s — Input string. String
c — Character to append if absent. String

Returned value

Returns string s with character c appended if s does not end with c. String

Examples

Usage example

SELECT appendTrailingCharIfAbsent('https://example.com', '/');

┌─appendTraili⋯.com', '/')─┐
│ https://example.com/     │
└──────────────────────────┘

ascii

Introduced in: v22.11

Returns the ASCII code point of the first character of string s as an Int32.

Syntax

ascii(s)

Arguments

s — String input. String

Returned value

Returns the ASCII code point of the first character. If s is empty, the result is 0. If the first character is not an ASCII character or not part of the Latin-1 supplement range of UTF-16, the result is undefined. Int32

Examples

Usage example

SELECT ascii('234')

┌─ascii('234')─┐
│           50 │
└──────────────┘

base32Decode

Introduced in: v25.6

Decodes a Base32 (RFC 4648) string. If the string is not valid Base32-encoded, an exception is thrown.

Syntax

base32Decode(encoded)

Arguments

encoded — String column or constant. String

Returned value

Returns a string containing the decoded value of the argument. String

Examples

Usage example

SELECT base32Decode('IVXGG33EMVSA====');

┌─base32Decode('IVXGG33EMVSA====')─┐
│ Encoded                          │
└──────────────────────────────────┘

base32Encode

Introduced in: v25.6

Encodes a string using Base32.

Syntax

base32Encode(plaintext)

Arguments

plaintext — Plaintext to encode. String

Returned value

Returns a string containing the encoded value of the argument. String or FixedString

Examples

Usage example

SELECT base32Encode('Encoded')

┌─base32Encode('Encoded')─┐
│ IVXGG33EMVSA====        │
└─────────────────────────┘

base58Decode

Introduced in: v22.7

Decodes a Base58 string. If the string is not valid Base58-encoded, an exception is thrown.

Syntax

base58Decode(encoded)

Arguments

encoded — String column or constant to decode. String

Returned value

Returns a string containing the decoded value of the argument. String

Examples

Usage example

SELECT base58Decode('JxF12TrwUP45BMd');

┌─base58Decode⋯rwUP45BMd')─┐
│ Hello World              │
└──────────────────────────┘

base58Encode

Introduced in: v22.7

Encodes a string using Base58 encoding.

Syntax

base58Encode(plaintext)

Arguments

plaintext — Plaintext to encode. String

Returned value

Returns a string containing the encoded value of the argument. String

Examples

Usage example

SELECT base58Encode('ClickHouse');

┌─base58Encode('ClickHouse')─┐
│ 4nhk8K7GHXf6zx             │
└────────────────────────────┘

base64Decode

Introduced in: v18.16

Decodes a string from Base64 representation, according to RFC 4648. Throws an exception in case of error.

Syntax

base64Decode(encoded)

Arguments

encoded — String column or constant to decode. If the string is not valid Base64-encoded, an exception is thrown. String

Returned value

Returns the decoded string. String

Examples

Usage example

SELECT base64Decode('Y2xpY2tob3VzZQ==')

┌─base64Decode('Y2xpY2tob3VzZQ==')─┐
│ clickhouse                       │
└──────────────────────────────────┘

base64Encode

Introduced in: v18.16

Encodes a string using Base64 representation, according to RFC 4648.

Syntax

base64Encode(plaintext)

Arguments

plaintext — Plaintext column or constant to decode. String

Returned value

Returns a string containing the encoded value of the argument. String

Examples

Usage example

SELECT base64Encode('clickhouse')

┌─base64Encode('clickhouse')─┐
│ Y2xpY2tob3VzZQ==           │
└────────────────────────────┘

base64URLDecode

Introduced in: v24.6

Decodes a string from Base64 representation using URL-safe alphabet, according to RFC 4648. Throws an exception in case of error.

Syntax

base64URLDecode(encoded)

Arguments

encoded — String column or constant to encode. If the string is not valid Base64-encoded, an exception is thrown. String

Returned value

Returns a string containing the decoded value of the argument. String

Examples

Usage example

SELECT base64URLDecode('aHR0cHM6Ly9jbGlja2hvdXNlLmNvbQ')

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

base64URLEncode

Introduced in: v18.16

Encodes a string using Base64 (RFC 4648) representation using URL-safe alphabet.

Syntax

base64URLEncode(plaintext)

Arguments

plaintext — Plaintext column or constant to encode. String

Returned value

Returns a string containing the encoded value of the argument. String

Examples

Usage example

SELECT base64URLEncode('https://clickhouse.com')

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

basename

Introduced in: v20.1

Extracts the tail of a string following its last slash or backslash. This function is often used to extract the filename from a path.

Syntax

basename(expr)

Arguments

expr — A string expression. Backslashes must be escaped. String

Returned value

Returns the tail of the input string after its last slash or backslash. If the input string ends with a slash or backslash, the function returns an empty string. Returns the original string if there are no slashes or backslashes. String

Examples

Extract filename from Unix path

SELECT 'some/long/path/to/file' AS a, basename(a)

┌─a──────────────────────┬─basename('some/long/path/to/file')─┐
│ some/long/path/to/file │ file                               │
└────────────────────────┴────────────────────────────────────┘

Extract filename from Windows path

SELECT 'some\\long\\path\\to\\file' AS a, basename(a)

┌─a──────────────────────┬─basename('some\\long\\path\\to\\file')─┐
│ some\long\path\to\file │ file                                   │
└────────────────────────┴────────────────────────────────────────┘

String with no path separators

SELECT 'some-file-name' AS a, basename(a)

┌─a──────────────┬─basename('some-file-name')─┐
│ some-file-name │ some-file-name             │
└────────────────┴────────────────────────────┘

byteHammingDistance

Introduced in: v23.9

Calculates the hamming distance between two byte strings.

Syntax

byteHammingDistance(s1, s2)

Arguments

s1 — First input string. String
s2 — Second input string. String

Returned value

Returns the Hamming distance between the two strings. UInt64

Examples

Usage example

SELECT byteHammingDistance('karolin', 'kathrin')

┌─byteHammingDistance('karolin', 'kathrin')─┐
│                                         3 │
└───────────────────────────────────────────┘

compareSubstrings

Introduced in: v25.2

Compares two strings lexicographically.

Syntax

compareSubstrings(s1, s2, s1_offset, s2_offset, num_bytes)

Arguments

s1 — The first string to compare. String
s2 — The second string to compare. String
s1_offset — The position (zero-based) in s1 from which the comparison starts. UInt*
s2_offset — The position (zero-based index) in s2 from which the comparison starts. UInt*
num_bytes — The maximum number of bytes to compare in both strings. If s1_offset (or s2_offset) + num_bytes exceeds the end of an input string, num_bytes will be reduced accordingly. UInt*

Returned value

Returns:

-1 if s1[s1_offset : s1_offset + num_bytes] < s2[s2_offset : s2_offset + num_bytes].
0 if s1[s1_offset : s1_offset + num_bytes] = s2[s2_offset : s2_offset + num_bytes].
1 if s1[s1_offset : s1_offset + num_bytes] > s2[s2_offset : s2_offset + num_bytes]. Int8

Examples

Usage example

SELECT compareSubstrings('Saxony', 'Anglo-Saxon', 0, 6, 5) AS result

┌─result─┐
│      0 │
└────────┘

concat

Introduced in: v1.1

Concatenates the given arguments.

Arguments which are not of types String or FixedString are converted to strings using their default serialization. As this decreases performance, it is not recommended to use non-String/FixedString arguments.

Syntax

concat([s1, s2, ...])

Arguments

s1, s2, ... — Any number of values of arbitrary type. Any

Returned value

Returns the String created by concatenating the arguments. If any of arguments is NULL, the function returns NULL. If there are no arguments, it returns an empty string. Nullable(String)

Examples

String concatenation

SELECT concat('Hello, ', 'World!')

┌─concat('Hello, ', 'World!')─┐
│ Hello, World!               │
└─────────────────────────────┘

Number concatenation

SELECT concat(42, 144)

┌─concat(42, 144)─┐
│ 42144           │
└─────────────────┘

concatAssumeInjective

Introduced in: v1.1

Like concat but assumes that concat(s1, s2, ...) → sn is injective, i.e, it returns different results for different arguments.

Can be used for optimization of GROUP BY.

Syntax

concatAssumeInjective([s1, s2, ...])

Arguments

s1, s2, ... — Any number of values of arbitrary type. String or FixedString

Returned value

Returns the string created by concatenating the arguments. If any of argument values is NULL, the function returns NULL. If no arguments are passed, it returns an empty string. String

Examples

Group by optimization

SELECT concat(key1, key2), sum(value) FROM key_val GROUP BY concatAssumeInjective(key1, key2)

┌─concat(key1, key2)─┬─sum(value)─┐
│ Hello, World!      │          3 │
│ Hello, World!      │          2 │
│ Hello, World       │          3 │
└────────────────────┴────────────┘

concatWithSeparator

Introduced in: v22.12

Concatenates the provided strings, separating them by the specified separator.

Syntax

concatWithSeparator(sep[, exp1, exp2, ...])

Arguments

sep — The separator to use. const String or const FixedString
exp1, exp2, ... — Expression to be concatenated. Arguments which are not of type String or FixedString are converted to strings using their default serialization. As this decreases performance, it is not recommended to use non-String/FixedString arguments. Any

Returned value

Returns the String created by concatenating the arguments. If any of the argument values is NULL, the function returns NULL. String

Examples

Usage example

SELECT concatWithSeparator('a', '1', '2', '3', '4')

┌─concatWithSeparator('a', '1', '2', '3', '4')─┐
│ 1a2a3a4                                      │
└──────────────────────────────────────────────┘

concatWithSeparatorAssumeInjective

Introduced in: v22.12

Like concatWithSeparator but assumes that concatWithSeparator(sep[,exp1, exp2, ... ]) → result is injective. A function is called injective if it returns different results for different arguments.

Can be used for optimization of GROUP BY.

Syntax

concatWithSeparatorAssumeInjective(sep[, exp1, exp2, ... ])

Arguments

sep — The separator to use. const String or const FixedString
exp1, exp2, ... — Expression to be concatenated. Arguments which are not of type String or FixedString are converted to strings using their default serialization. As this decreases performance, it is not recommended to use non-String/FixedString arguments. String or FixedString

Returned value

Returns the String created by concatenating the arguments. If any of the argument values is NULL, the function returns NULL. String

Examples

Usage example

CREATE TABLE user_data (
user_id UInt32,
first_name String,
last_name String,
score UInt32
)
ENGINE = MergeTree
ORDER BY tuple();

INSERT INTO user_data VALUES
(1, 'John', 'Doe', 100),
(2, 'Jane', 'Smith', 150),
(3, 'John', 'Wilson', 120),
(4, 'Jane', 'Smith', 90);

SELECT
    concatWithSeparatorAssumeInjective('-', first_name, last_name) as full_name,
    sum(score) as total_score
FROM user_data
GROUP BY concatWithSeparatorAssumeInjective('-', first_name, last_name);

┌─full_name───┬─total_score─┐
│ Jane-Smith  │         240 │
│ John-Doe    │         100 │
│ John-Wilson │         120 │
└─────────────┴─────────────┘

conv

Introduced in: v1.1

Converts numbers between different number bases.

The function converts a number from one base to another. It supports bases from 2 to 36. For bases higher than 10, letters A-Z (case insensitive) are used to represent digits 10-35.

This function is compatible with MySQL's CONV() function.

Syntax

conv(number, from_base, to_base)

Arguments

number — The number to convert. Can be a string or numeric type. - from_base — The source base (2-36). Must be an integer. - to_base — The target base (2-36). Must be an integer.

Returned value

String representation of the number in the target base.

Examples

Convert decimal to binary

SELECT conv('10', 10, 2)

Convert hexadecimal to decimal

SELECT conv('FF', 16, 10)

Convert with negative number

SELECT conv('-1', 10, 16)

FFFFFFFFFFFFFFFF

Convert binary to octal

SELECT conv('1010', 2, 8)

convertCharset

Introduced in: v1.1

Returns string s converted from the encoding from to encoding to.

Syntax

convertCharset(s, from, to)

Arguments

s — Input string. String
from — Source character encoding. String
to — Target character encoding. String

Returned value

Returns string s converted from encoding from to encoding to. String

Examples

Usage example

SELECT convertCharset('Café', 'UTF-8', 'ISO-8859-1');

┌─convertChars⋯SO-8859-1')─┐
│ Caf�                     │
└──────────────────────────┘

damerauLevenshteinDistance

Introduced in: v24.1

Calculates the Damerau-Levenshtein distance between two byte strings.

Syntax

damerauLevenshteinDistance(s1, s2)

Arguments

s1 — First input string. String
s2 — Second input string. String

Returned value

Returns the Damerau-Levenshtein distance between the two strings. UInt64

Examples

Usage example

SELECT damerauLevenshteinDistance('clickhouse', 'mouse')

┌─damerauLevenshteinDistance('clickhouse', 'mouse')─┐
│                                                 6 │
└───────────────────────────────────────────────────┘

decodeHTMLComponent

Introduced in: v23.9

Decodes HTML entities in a string to their corresponding characters.

Syntax

decodeHTMLComponent(s)

Arguments

s — String containing HTML entities to decode. String

Returned value

Returns the string with HTML entities decoded. String

Examples

Usage example

SELECT decodeHTMLComponent('&lt;div&gt;Hello &amp; &quot;World&quot;&lt;/div&gt;')

┌─decodeHTMLComponent('&lt;div&gt;Hello &amp; &quot;World&quot;&lt;/div&gt;')─┐
│ <div>Hello & "World"</div>                                                  │
└─────────────────────────────────────────────────────────────────────────────┘

decodeXMLComponent

Introduced in: v21.2

Decodes XML entities in a string to their corresponding characters.

Syntax

decodeXMLComponent(s)

Arguments

s — String containing XML entities to decode. String

Returned value

Returns the provided string with XML entities decoded. String

Examples

Usage example

SELECT decodeXMLComponent('&lt;tag&gt;Hello &amp; World&lt;/tag&gt;')

┌─decodeXMLCom⋯;/tag&gt;')─┐
│ <tag>Hello & World</tag> │
└──────────────────────────┘

editDistance

Introduced in: v23.9

Calculates the edit distance between two byte strings.

Syntax

editDistance(s1, s2)

Arguments

s1 — First input string. String
s2 — Second input string. String

Returned value

Returns the edit distance between the two strings. UInt64

Examples

Usage example

SELECT editDistance('clickhouse', 'mouse')

┌─editDistance('clickhouse', 'mouse')─┐
│                                   6 │
└─────────────────────────────────────┘

editDistanceUTF8

Introduced in: v24.6

Calculates the edit distance between two UTF8 strings.

Syntax

editDistanceUTF8(s1, s2)

Arguments

s1 — First input string. String
s2 — Second input string. String

Returned value

Returns the edit distance between the two UTF8 strings. UInt64

Examples

Usage example

SELECT editDistanceUTF8('我是谁', '我是我')

┌─editDistanceUTF8('我是谁', '我是我')──┐
│                                   1 │
└─────────────────────────────────────┘

encodeXMLComponent

Introduced in: v21.1

Escapes characters to place string into XML text node or attribute.

Syntax

encodeXMLComponent(s)

Arguments

s — String to escape. String

Returned value

Returns the escaped string. String

Examples

Usage example

SELECT
    '<tag>Hello & "World"</tag>' AS original,
    encodeXMLComponent('<tag>Hello & "World"</tag>') AS xml_encoded;

┌─original───────────────────┬─xml_encoded──────────────────────────────────────────┐
│ <tag>Hello & "World"</tag> │ &lt;tag&gt;Hello &amp; &quot;World&quot;&lt;/tag&gt; │
└────────────────────────────┴──────────────────────────────────────────────────────┘

endsWith

Introduced in: v1.1

Checks whether a string ends with the provided suffix.

Syntax

endsWith(s, suffix)

Arguments

s — String to check. String
suffix — Suffix to check for. String

Returned value

Returns 1 if s ends with suffix, otherwise 0. UInt8

Examples

Usage example

SELECT endsWith('ClickHouse', 'House');

┌─endsWith('Cl⋯', 'House')─┐
│                        1 │
└──────────────────────────┘

endsWithCaseInsensitive

Introduced in: v25.9

Checks whether a string ends with the provided case-insensitive suffix.

Syntax

endsWithCaseInsensitive(s, suffix)

Arguments

s — String to check. String
suffix — Case-insensitive suffix to check for. String

Returned value

Returns 1 if s ends with case-insensitive suffix, otherwise 0. UInt8

Examples

Usage example

SELECT endsWithCaseInsensitive('ClickHouse', 'HOUSE');

┌─endsWithCaseInsensitive('Cl⋯', 'HOUSE')─┐
│                                       1 │
└─────────────────────────────────────────┘

endsWithCaseInsensitiveUTF8

Introduced in: v25.9

Returns whether string s ends with case-insensitive suffix. Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Syntax

endsWithCaseInsensitiveUTF8(s, suffix)

Arguments

s — String to check. String
suffix — Case-insensitive suffix to check for. String

Returned value

Returns 1 if s ends with case-insensitive suffix, otherwise 0. UInt8

Examples

Usage example

SELECT endsWithCaseInsensitiveUTF8('данных', 'ых');

┌─endsWithCaseInsensitiveUTF8('данных', 'ых')─┐
│                                           1 │
└─────────────────────────────────────────────┘

endsWithUTF8

Introduced in: v23.8

Returns whether string s ends with suffix. Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Syntax

endsWithUTF8(s, suffix)

Arguments

s — String to check. String
suffix — Suffix to check for. String

Returned value

Returns 1 if s ends with suffix, otherwise 0. UInt8

Examples

Usage example

SELECT endsWithUTF8('данных', 'ых');

┌─endsWithUTF8('данных', 'ых')─┐
│                            1 │
└──────────────────────────────┘

extractTextFromHTML

Introduced in: v21.3

Extracts text content from HTML or XHTML.

This function removes HTML tags, comments, and script/style elements, leaving only the text content. It handles:

Removal of all HTML/XML tags
Removal of comments ()
Removal of script and style elements with their content
Processing of CDATA sections (copied verbatim)
Proper whitespace handling and normalization

Note: HTML entities are not decoded and should be processed with a separate function if needed.

Syntax

extractTextFromHTML(html)

Arguments

html — String containing HTML content to extract text from. String

Returned value

Returns the extracted text content with normalized whitespace. String

Examples

Usage example

SELECT extractTextFromHTML('
<html>
    <head><title>Page Title</title></head>
    <body>
        <p>Hello <b>World</b>!</p>
        <script>alert("test");</script>
        <!-- comment -->
    </body>
</html>
');

┌─extractTextFromHTML('<html><head>...')─┐
│ Page Title Hello World!                │
└────────────────────────────────────────┘

firstLine

Introduced in: v23.7

Returns the first line of a multi-line string.

Syntax

firstLine(s)

Arguments

s — Input string. String

Returned value

Returns the first line of the input string or the whole string if there are no line separators. String

Examples

Usage example

SELECT firstLine('foo\\nbar\\nbaz')

┌─firstLine('foo\nbar\nbaz')─┐
│ foo                        │
└────────────────────────────┘

idnaDecode

Introduced in: v24.1

Syntax

idnaDecode(s)

Arguments

s — Input string. String

Returned value

Returns a Unicode (UTF-8) representation of the input string according to the IDNA mechanism of the input value. String

Examples

Usage example

SELECT idnaDecode('xn--strae-oqa.xn--mnchen-3ya.de')

┌─idnaDecode('xn--strae-oqa.xn--mnchen-3ya.de')─┐
│ straße.münchen.de                             │
└───────────────────────────────────────────────┘

idnaEncode

Introduced in: v24.1

Note

No percent decoding or trimming of tabs, spaces or control characters is performed.

Syntax

idnaEncode(s)

Arguments

s — Input string. String

Returned value

Returns an ASCII representation of the input string according to the IDNA mechanism of the input value. String

Examples

Usage example

SELECT idnaEncode('straße.münchen.de')

┌─idnaEncode('straße.münchen.de')─────┐
│ xn--strae-oqa.xn--mnchen-3ya.de     │
└─────────────────────────────────────┘

initcap

Introduced in: v23.7

Converts the first letter of each word to upper case and the rest to lower case. Words are sequences of alphanumeric characters separated by non-alphanumeric characters.

Note

Because initcap converts only the first letter of each word to upper case you may observe unexpected behaviour for words containing apostrophes or capital letters. This is a known behaviour and there are no plans to fix it currently.

Syntax

initcap(s)

Arguments

s — Input string. String

Returned value

Returns s with the first letter of each word converted to upper case. String

Examples

Usage example

SELECT initcap('building for fast')

┌─initcap('building for fast')─┐
│ Building For Fast            │
└──────────────────────────────┘

Example of known behavior for words containing apostrophes or capital letters

SELECT initcap('John''s cat won''t eat.');

┌─initcap('Joh⋯n\'t eat.')─┐
│ John'S Cat Won'T Eat.    │
└──────────────────────────┘

initcapUTF8

Introduced in: v23.7

Like initcap, initcapUTF8 converts the first letter of each word to upper case and the rest to lower case. Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Note

Syntax

initcapUTF8(s)

Arguments

s — Input string. String

Returned value

Returns s with the first letter of each word converted to upper case. String

Examples

Usage example

SELECT initcapUTF8('не тормозит')

┌─initcapUTF8('не тормозит')─┐
│ Не Тормозит                │
└────────────────────────────┘

isValidASCII

Introduced in: v25.9

Returns 1 if the input String or FixedString contains only ASCII bytes (0x00–0x7F), otherwise 0.

Syntax

Arguments

None. Returned value

Examples

isValidASCII

SELECT isValidASCII('hello') AS is_ascii, isValidASCII('你好') AS is_not_ascii

isValidUTF8

Introduced in: v20.1

Checks if the set of bytes constitutes valid UTF-8-encoded text.

Syntax

isValidUTF8(s)

Arguments

s — The string to check for UTF-8 encoded validity. String

Returned value

Returns 1, if the set of bytes constitutes valid UTF-8-encoded text, otherwise 0. UInt8

Examples

Usage example

SELECT isValidUTF8('\\xc3\\xb1') AS valid, isValidUTF8('\\xc3\\x28') AS invalid

┌─valid─┬─invalid─┐
│     1 │       0 │
└───────┴─────────┘

jaroSimilarity

Introduced in: v24.1

Calculates the Jaro similarity between two byte strings.

Syntax

jaroSimilarity(s1, s2)

Arguments

s1 — First input string. String
s2 — Second input string. String

Returned value

Returns the Jaro similarity between the two strings. Float64

Examples

Usage example

SELECT jaroSimilarity('clickhouse', 'click')

┌─jaroSimilarity('clickhouse', 'click')─┐
│                    0.8333333333333333 │
└───────────────────────────────────────┘

jaroWinklerSimilarity

Introduced in: v24.1

Calculates the Jaro-Winkler similarity between two byte strings.

Syntax

jaroWinklerSimilarity(s1, s2)

Arguments

s1 — First input string. String
s2 — Second input string. String

Returned value

Returns the Jaro-Winkler similarity between the two strings. Float64

Examples

Usage example

SELECT jaroWinklerSimilarity('clickhouse', 'click')

┌─jaroWinklerSimilarity('clickhouse', 'click')─┐
│                           0.8999999999999999 │
└──────────────────────────────────────────────┘

left

Introduced in: v22.1

Returns a substring of string s with a specified offset starting from the left.

Syntax

left(s, offset)

Arguments

s — The string to calculate a substring from. String or FixedString
offset — The number of bytes of the offset. (U)Int*

Returned value

Returns:

For positive offset, a substring of s with offset many bytes, starting from the left of the string.
For negative offset, a substring of s with length(s) - |offset| bytes, starting from the left of the string.
An empty string if length is 0. String

Examples

Positive offset

SELECT left('Hello World', 5)

Helllo

Negative offset

SELECT left('Hello World', -6)

Hello

leftPad

Introduced in: v21.8

Pads a string from the left with spaces or with a specified string (multiple times, if needed) until the resulting string reaches the specified length.

Syntax

leftPad(string, length[, pad_string])

Arguments

string — Input string that should be padded. String
length — The length of the resulting string. If the value is smaller than the input string length, then the input string is shortened to length characters. (U)Int*
pad_string — Optional. The string to pad the input string with. If not specified, then the input string is padded with spaces. String

Returned value

Returns a left-padded string of the given length. String

Examples

Usage example

SELECT leftPad('abc', 7, '*'), leftPad('def', 7)

┌─leftPad('abc', 7, '*')─┬─leftPad('def', 7)─┐
│ ****abc                │     def           │
└────────────────────────┴───────────────────┘

leftPadUTF8

Introduced in: v21.8

Pads a UTF8 string from the left with spaces or a specified string (multiple times, if needed) until the resulting string reaches the given length. Unlike leftPad which measures the string length in bytes, the string length is measured in code points.

Syntax

leftPadUTF8(string, length[, pad_string])

Arguments

string — Input string that should be padded. String
length — The length of the resulting string. If the value is smaller than the input string length, then the input string is shortened to length characters. (U)Int*
pad_string — Optional. The string to pad the input string with. If not specified, then the input string is padded with spaces. String

Returned value

Returns a left-padded string of the given length. String

Examples

Usage example

SELECT leftPadUTF8('абвг', 7, '*'), leftPadUTF8('дежз', 7)

┌─leftPadUTF8('абвг', 7, '*')─┬─leftPadUTF8('дежз', 7)─┐
│ ***абвг                     │    дежз                │
└─────────────────────────────┴────────────────────────┘

leftUTF8

Introduced in: v22.1

Returns a substring of a UTF-8-encoded string s with a specified offset starting from the left.

Syntax

leftUTF8(s, offset)

Arguments

s — The UTF-8 encoded string to calculate a substring from. String or FixedString
offset — The number of bytes of the offset. (U)Int*

Returned value

Returns:

For positive offset, a substring of s with offset many bytes, starting from the left of the string.\n"
For negative offset, a substring of s with length(s) - |offset| bytes, starting from the left of the string.\n"
An empty string if length is 0. String

Examples

Positive offset

SELECT leftUTF8('Привет', 4)

Прив

Negative offset

SELECT leftUTF8('Привет', -4)

Пр

lengthUTF8

Introduced in: v1.1

Syntax

lengthUTF8(s)

Arguments

s — String containing valid UTF-8 encoded text. String

Returned value

Length of the string s in Unicode code points. UInt64

Examples

Usage example

SELECT lengthUTF8('Здравствуй, мир!')

┌─lengthUTF8('Здравствуй, мир!')─┐
│                             16 │
└────────────────────────────────┘

lower

Introduced in: v1.1

Converts an ASCII string to lowercase.

Syntax

lower(s)

Arguments

s — A string to convert to lowercase. String

Returned value

Returns a lowercase string from s. String

Examples

Usage example

SELECT lower('CLICKHOUSE')

┌─lower('CLICKHOUSE')─┐
│ clickhouse          │
└─────────────────────┘

lowerUTF8

Introduced in: v1.1

Converts a string to lowercase, assuming that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Syntax

lowerUTF8(input)

Arguments

input — Input string to convert to lowercase. String

Returned value

Returns a lowercase string. String

Examples

first

SELECT lowerUTF8('München') as Lowerutf8;

münchen

normalizeUTF8NFC

Introduced in: v21.11

Normalizes a UTF-8 string according to the NFC normalization form.

Syntax

normalizeUTF8NFC(str)

Arguments

str — UTF-8 encoded input string. String

Returned value

Returns the NFC normalized form of the UTF-8 string. String

Examples

Usage example

SELECT
'é' AS original, -- e + combining acute accent (U+0065 + U+0301)
length(original),
normalizeUTF8NFC('é') AS nfc_normalized, -- é (U+00E9)
length(nfc_normalized);

┌─original─┬─length(original)─┬─nfc_normalized─┬─length(nfc_normalized)─┐
│ é        │                2 │ é              │                      2 │
└──────────┴──────────────────┴────────────────┴────────────────────────┘

normalizeUTF8NFD

Introduced in: v21.11

Normalizes a UTF-8 string according to the NFD normalization form.

Syntax

normalizeUTF8NFD(str)

Arguments

str — UTF-8 encoded input string. String

Returned value

Returns the NFD normalized form of the UTF-8 string. String

Examples

Usage example

SELECT
    'é' AS original, -- é (U+00E9)
    length(original),
    normalizeUTF8NFD('é') AS nfd_normalized, -- e + combining acute (U+0065 + U+0301)
    length(nfd_normalized);

┌─original─┬─length(original)─┬─nfd_normalized─┬─length(nfd_normalized)─┐
│ é        │                2 │ é              │                      3 │
└──────────┴──────────────────┴────────────────┴────────────────────────┘

normalizeUTF8NFKC

Introduced in: v21.11

Normalizes a UTF-8 string according to the NFKC normalization form.

Syntax

normalizeUTF8NFKC(str)

Arguments

str — UTF-8 encoded input string. String

Returned value

Returns the NFKC normalized form of the UTF-8 string. String

Examples

Usage example

SELECT
    '① ② ③' AS original,                            -- Circled number characters
    normalizeUTF8NFKC('① ② ③') AS nfkc_normalized;  -- Converts to 1 2 3

┌─original─┬─nfkc_normalized─┐
│ ① ② ③  │ 1 2 3           │
└──────────┴─────────────────┘

normalizeUTF8NFKD

Introduced in: v21.11

Normalizes a UTF-8 string according to the NFKD normalization form.

Syntax

normalizeUTF8NFKD(str)

Arguments

str — UTF-8 encoded input string. String

Returned value

Returns the NFKD normalized form of the UTF-8 string. String

Examples

Usage example

SELECT
    'H₂O²' AS original,                            -- H + subscript 2 + O + superscript 2
    normalizeUTF8NFKD('H₂O²') AS nfkd_normalized;  -- Converts to H 2 O 2

┌─original─┬─nfkd_normalized─┐
│ H₂O²     │ H2O2            │
└──────────┴─────────────────┘

punycodeDecode

Introduced in: v24.1

Returns the UTF8-encoded plaintext of a Punycode-encoded string. If no valid Punycode-encoded string is given, an exception is thrown.

Syntax

punycodeDecode(s)

Arguments

s — Punycode-encoded string. String

Returned value

Returns the plaintext of the input value. String

Examples

Usage example

SELECT punycodeDecode('Mnchen-3ya')

┌─punycodeDecode('Mnchen-3ya')─┐
│ München                      │
└──────────────────────────────┘

punycodeEncode

Introduced in: v24.1

Returns the Punycode representation of a string. The string must be UTF8-encoded, otherwise the behavior is undefined.

Syntax

punycodeEncode(s)

Arguments

s — Input value. String

Returned value

Returns a Punycode representation of the input value. String

Examples

Usage example

SELECT punycodeEncode('München')

┌─punycodeEncode('München')─┐
│ Mnchen-3ya                │
└───────────────────────────┘

repeat

Introduced in: v20.1

Concatenates a string as many times with itself as specified.

Syntax

repeat(s, n)

Arguments

s — The string to repeat. String
n — The number of times to repeat the string. (U)Int*

Returned value

A string containing string s repeated n times. If n is negative, the function returns the empty string. String

Examples

Usage example

SELECT repeat('abc', 10)

┌─repeat('abc', 10)──────────────┐
│ abcabcabcabcabcabcabcabcabcabc │
└────────────────────────────────┘

reverseUTF8

Introduced in: v1.1

Reverses a sequence of Unicode code points in a string. Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Syntax

reverseUTF8(s)

Arguments

s — String containing valid UTF-8 encoded text. String

Returned value

Returns a string with the sequence of Unicode code points reversed. String

Examples

Usage example

SELECT reverseUTF8('ClickHouse')

esuoHkcilC

right

Introduced in: v22.1

Returns a substring of string s with a specified offset starting from the right.

Syntax

right(s, offset)

Arguments

s — The string to calculate a substring from. String or FixedString
offset — The number of bytes of the offset. (U)Int*

Returned value

Returns:

For positive offset, a substring of s with offset many bytes, starting from the right of the string.
For negative offset, a substring of s with length(s) - |offset| bytes, starting from the right of the string.
An empty string if length is 0. String

Examples

Positive offset

SELECT right('Hello', 3)

llo

Negative offset

SELECT right('Hello', -3)

lo

rightPad

Introduced in: v21.8

Pads a string from the right with spaces or with a specified string (multiple times, if needed) until the resulting string reaches the specified length.

Syntax

rightPad(string, length[, pad_string])

Arguments

string — Input string that should be padded. String
length — The length of the resulting string. If the value is smaller than the input string length, then the input string is shortened to length characters. (U)Int*
pad_string — Optional. The string to pad the input string with. If not specified, then the input string is padded with spaces. String

Returned value

Returns a right-padded string of the given length. String

Examples

Usage example

SELECT rightPad('abc', 7, '*'), rightPad('abc', 7)

┌─rightPad('abc', 7, '*')─┬─rightPad('abc', 7)─┐
│ abc****                 │ abc                │
└─────────────────────────┴────────────────────┘

rightPadUTF8

Introduced in: v21.8

Pads the string from the right with spaces or a specified string (multiple times, if needed) until the resulting string reaches the given length. Unlike rightPad which measures the string length in bytes, the string length is measured in code points.

Syntax

rightPadUTF8(string, length[, pad_string])

Arguments

string — Input string that should be padded. String
length — The length of the resulting string. If the value is smaller than the input string length, then the input string is shortened to length characters. (U)Int*
pad_string — Optional. The string to pad the input string with. If not specified, then the input string is padded with spaces. String

Returned value

Returns a right-padded string of the given length. String

Examples

Usage example

SELECT rightPadUTF8('абвг', 7, '*'), rightPadUTF8('абвг', 7)

┌─rightPadUTF8('абвг', 7, '*')─┬─rightPadUTF8('абвг', 7)─┐
│ абвг***                      │ абвг                    │
└──────────────────────────────┴─────────────────────────┘

rightUTF8

Introduced in: v22.1

Returns a substring of UTF-8 encoded string s with a specified offset starting from the right.

Syntax

rightUTF8(s, offset)

Arguments

s — The UTF-8 encoded string to calculate a substring from. String or FixedString
offset — The number of bytes of the offset. (U)Int*

Returned value

Returns:

For positive offset, a substring of s with offset many bytes, starting from the right of the string.
For negative offset, a substring of s with length(s) - |offset| bytes, starting from the right of the string.
An empty string if length is 0. String

Examples

Positive offset

SELECT rightUTF8('Привет', 4)

ивет

Negative offset

SELECT rightUTF8('Привет', -4)

ет

soundex

Introduced in: v23.4

Returns the Soundex code of a string.

Syntax

soundex(s)

Arguments

s — Input string. String

Returned value

Returns the Soundex code of the input string. String

Examples

Usage example

SELECT soundex('aksel')

┌─soundex('aksel')─┐
│ A240             │
└──────────────────┘

space

Introduced in: v23.5

Concatenates a space ( ) as many times with itself as specified.

Syntax

space(n)

Arguments

n — The number of times to repeat the space. (U)Int*

Returned value

Returns astring containing a space repeated n times. If n <= 0, the function returns the empty string. String

Examples

Usage example

SELECT space(3) AS res, length(res);

┌─res─┬─length(res)─┐
│     │           3 │
└─────┴─────────────┘

sparseGrams

Introduced in: v25.5

Syntax

sparseGrams(s[, min_ngram_length, max_ngram_length])

Arguments

s — An input string. String
min_ngram_length — Optional. The minimum length of extracted ngram. The default and minimal value is 3. UInt*
max_ngram_length — Optional. The maximum length of extracted ngram. The default value is 100. Should be not less than min_ngram_length. UInt*

Returned value

Returns an array of selected substrings. Array(String)

Examples

Usage example

SELECT sparseGrams('alice', 3)

┌─sparseGrams('alice', 3)────────────┐
│ ['ali','lic','lice','ice']         │
└────────────────────────────────────┘

sparseGramsHashes

Introduced in: v25.5

Syntax

sparseGramsHashes(s[, min_ngram_length, max_ngram_length])

Arguments

s — An input string. String
min_ngram_length — Optional. The minimum length of extracted ngram. The default and minimal value is 3. UInt*
max_ngram_length — Optional. The maximum length of extracted ngram. The default value is 100. Should be not less than min_ngram_length. UInt*

Returned value

Returns an array of selected substrings CRC32 hashes. Array(UInt32)

Examples

Usage example

SELECT sparseGramsHashes('alice', 3)

┌─sparseGramsHashes('alice', 3)──────────────────────┐
│ [1481062250,2450405249,4012725991,1918774096]      │
└────────────────────────────────────────────────────┘

sparseGramsHashesUTF8

Introduced in: v25.5

Finds hashes of all substrings of a given UTF-8 string that have a length of at least n, where the hashes of the (n-1)-grams at the borders of the substring are strictly greater than those of any (n-1)-gram inside the substring. Expects UTF-8 string, throws an exception in case of invalid UTF-8 sequence. Uses CRC32 as a hash function.

Syntax

sparseGramsHashesUTF8(s[, min_ngram_length, max_ngram_length])

Arguments

s — An input string. String
min_ngram_length — Optional. The minimum length of extracted ngram. The default and minimal value is 3. UInt*
max_ngram_length — Optional. The maximum length of extracted ngram. The default value is 100. Should be not less than min_ngram_length. UInt*

Returned value

Returns an array of selected UTF-8 substrings CRC32 hashes. Array(UInt32)

Examples

Usage example

SELECT sparseGramsHashesUTF8('алиса', 3)

┌─sparseGramsHashesUTF8('алиса', 3)─┐
│ [4178533925,3855635300,561830861] │
└───────────────────────────────────┘

sparseGramsUTF8

Introduced in: v25.5

Finds all substrings of a given UTF-8 string that have a length of at least n, where the hashes of the (n-1)-grams at the borders of the substring are strictly greater than those of any (n-1)-gram inside the substring. Expects a UTF-8 string, throws an exception in case of an invalid UTF-8 sequence. Uses CRC32 as a hash function.

Syntax

sparseGramsUTF8(s[, min_ngram_length, max_ngram_length])

Arguments

s — An input string. String
min_ngram_length — Optional. The minimum length of extracted ngram. The default and minimal value is 3. UInt*
max_ngram_length — Optional. The maximum length of extracted ngram. The default value is 100. Should be not less than min_ngram_length. UInt*

Returned value

Returns an array of selected UTF-8 substrings. Array(String)

Examples

Usage example

SELECT sparseGramsUTF8('алиса', 3)

┌─sparseGramsUTF8('алиса', 3)─┐
│ ['али','лис','иса']         │
└─────────────────────────────┘

startsWith

Introduced in: v1.1

Checks whether a string begins with the provided string.

Syntax

startsWith(s, prefix)

Arguments

s — String to check. String
prefix — Prefix to check for. String

Returned value

Returns 1 if s starts with prefix, otherwise 0. UInt8

Examples

Usage example

SELECT startsWith('ClickHouse', 'Click');

┌─startsWith('⋯', 'Click')─┐
│                        1 │
└──────────────────────────┘

startsWithCaseInsensitive

Introduced in: v25.9

Checks whether a string begins with the provided case-insensitive string.

Syntax

startsWithCaseInsensitive(s, prefix)

Arguments

s — String to check. String
prefix — Case-insensitive prefix to check for. String

Returned value

Returns 1 if s starts with case-insensitive prefix, otherwise 0. UInt8

Examples

Usage example

SELECT startsWithCaseInsensitive('ClickHouse', 'CLICK');

┌─startsWithCaseInsensitive('⋯', 'CLICK')─┐
│                                       1 │
└─────────────────────────────────────────┘

startsWithCaseInsensitiveUTF8

Introduced in: v25.9

Checks if a string starts with the provided case-insensitive prefix. Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Syntax

startsWithCaseInsensitiveUTF8(s, prefix)

Arguments

s — String to check. String
prefix — Case-insensitive prefix to check for. String

Returned value

Returns 1 if s starts with case-insensitive prefix, otherwise 0. UInt8

Examples

Usage example

SELECT startsWithCaseInsensitiveUTF8('приставка', 'при')

┌─startsWithUT⋯ка', 'при')─┐
│                        1 │
└──────────────────────────┘

startsWithUTF8

Introduced in: v23.8

Checks if a string starts with the provided prefix. Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Syntax

startsWithUTF8(s, prefix)

Arguments

s — String to check. String
prefix — Prefix to check for. String

Returned value

Returns 1 if s starts with prefix, otherwise 0. UInt8

Examples

Usage example

SELECT startsWithUTF8('приставка', 'при')

┌─startsWithUT⋯ка', 'при')─┐
│                        1 │
└──────────────────────────┘

stringBytesEntropy

Introduced in: v25.6

Calculates Shannon's entropy of byte distribution in a string.

Syntax

stringBytesEntropy(s)

Arguments

s — The string to analyze. String

Returned value

Returns Shannon's entropy of byte distribution in the string. Float64

Examples

Usage example

SELECT stringBytesEntropy('Hello, world!')

┌─stringBytesEntropy('Hello, world!')─┐
│                         3.07049960  │
└─────────────────────────────────────┘

stringBytesUniq

Introduced in: v25.6

Counts the number of distinct bytes in a string.

Syntax

stringBytesUniq(s)

Arguments

s — The string to analyze. String

Returned value

Returns the number of distinct bytes in the string. UInt16

Examples

Usage example

SELECT stringBytesUniq('Hello')

┌─stringBytesUniq('Hello')─┐
│                        4 │
└──────────────────────────┘

stringJaccardIndex

Introduced in: v23.11

Calculates the Jaccard similarity index between two byte strings.

Syntax

stringJaccardIndex(s1, s2)

Arguments

s1 — First input string. String
s2 — Second input string. String

Returned value

Returns the Jaccard similarity index between the two strings. Float64

Examples

Usage example

SELECT stringJaccardIndex('clickhouse', 'mouse')

┌─stringJaccardIndex('clickhouse', 'mouse')─┐
│                                       0.4 │
└───────────────────────────────────────────┘

stringJaccardIndexUTF8

Introduced in: v23.11

Like stringJaccardIndex but for UTF8-encoded strings.

Syntax

stringJaccardIndexUTF8(s1, s2)

Arguments

s1 — First input UTF8 string. String
s2 — Second input UTF8 string. String

Returned value

Returns the Jaccard similarity index between the two UTF8 strings. Float64

Examples

Usage example

SELECT stringJaccardIndexUTF8('我爱你', '我也爱你')

┌─stringJaccardIndexUTF8('我爱你', '我也爱你')─┐
│                                       0.75 │
└─────────────────────────────────────────────┘

substring

Introduced in: v1.1

Returns the substring of a string s which starts at the specified byte index offset. Byte counting starts from 1 with the following logic:

If offset is 0, an empty string is returned.
If offset is negative, the substring starts pos characters from the end of the string, rather than from the beginning.

An optional argument length specifies the maximum number of bytes the returned substring may have.

Syntax

substring(s, offset[, length])

Arguments

s — The string to calculate a substring from. String or FixedString or Enum
offset — The starting position of the substring in s. (U)Int*
length — Optional. The maximum length of the substring. (U)Int*

Returned value

Returns a substring of s with length many bytes, starting at index offset. String

Examples

Basic usage

SELECT 'database' AS db, substr(db, 5), substr(db, 5, 1)

┌─db───────┬─substring('database', 5)─┬─substring('database', 5, 1)─┐
│ database │ base                     │ b                           │
└──────────┴──────────────────────────┴─────────────────────────────┘

substringIndex

Introduced in: v23.7

Returns the substring of s before count occurrences of the delimiter delim, as in Spark or MySQL.

Syntax

substringIndex(s, delim, count)

Arguments

s — The string to extract substring from. String
delim — The character to split. String
count — The number of occurrences of the delimiter to count before extracting the substring. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. UInt or Int

Returned value

Returns a substring of s before count occurrences of delim. String

Examples

Usage example

SELECT substringIndex('www.clickhouse.com', '.', 2)

┌─substringIndex('www.clickhouse.com', '.', 2)─┐
│ www.clickhouse                               │
└──────────────────────────────────────────────┘

substringIndexUTF8

Introduced in: v23.7

Returns the substring of s before count occurrences of the delimiter delim, specifically for Unicode code points. Assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Syntax

substringIndexUTF8(s, delim, count)

Arguments

s — The string to extract substring from. String
delim — The character to split. String
count — The number of occurrences of the delimiter to count before extracting the substring. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. UInt or Int

Returned value

Returns a substring of s before count occurrences of delim. String

Examples

UTF8 example

SELECT substringIndexUTF8('www.straßen-in-europa.de', '.', 2)

www.straßen-in-europa

substringUTF8

Introduced in: v1.1

Returns the substring of a string s which starts at the specified byte index offset for Unicode code points. Byte counting starts from 1 with the following logic:

If offset is 0, an empty string is returned.
If offset is negative, the substring starts pos characters from the end of the string, rather than from the beginning.

An optional argument length specifies the maximum number of bytes the returned substring may have.

Note

This function assumes that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Syntax

substringUTF8(s, offset[, length])

Arguments

s — The string to calculate a substring from. String or FixedString or Enum
offset — The starting position of the substring in s. Int or UInt
length — The maximum length of the substring. Optional. Int or UInt

Returned value

Returns a substring of s with length many bytes, starting at index offset. String

Examples

Usage example

SELECT 'Täglich grüßt das Murmeltier.' AS str, substringUTF8(str, 9), substringUTF8(str, 9, 5)

Täglich grüßt das Murmeltier.    grüßt das Murmeltier.    grüßt

toValidUTF8

Introduced in: v20.1

Converts a string to valid UTF-8 encoding by replacing any invalid UTF-8 characters with the replacement character � (U+FFFD). When multiple consecutive invalid characters are found, they are collapsed into a single replacement character.

Syntax

toValidUTF8(s)

Arguments

s — Any set of bytes represented as the String data type object. String

Returned value

Returns a valid UTF-8 string. String

Examples

Usage example

SELECT toValidUTF8('\\x61\\xF0\\x80\\x80\\x80b')

c
┌─toValidUTF8('a����b')─┐
│ a�b                   │
└───────────────────────┘

trimBoth

Introduced in: v20.1

Removes the specified characters from the start and end of a string. By default, removes common whitespace (ASCII) characters.

Syntax

trimBoth(s[, trim_characters])

Arguments

s — String to trim. String
trim_characters — Optional. Characters to trim. If not specified, common whitespace characters are removed. String

Returned value

Returns the string with specified characters trimmed from both ends. String

Examples

Usage example

SELECT trimBoth('$$ClickHouse$$', '$')

┌─trimBoth('$$⋯se$$', '$')─┐
│ ClickHouse               │
└──────────────────────────┘

trimLeft

Introduced in: v20.1

Removes the specified characters from the start of a string. By default, removes common whitespace (ASCII) characters.

Syntax

trimLeft(input[, trim_characters])

Arguments

input — String to trim. String
trim_characters — Optional. Characters to trim. If not specified, common whitespace characters are removed. String

Returned value

Returns the string with specified characters trimmed from the left. String

Examples

Usage example

SELECT trimLeft('ClickHouse', 'Click');

┌─trimLeft('Cl⋯', 'Click')─┐
│ House                    │
└──────────────────────────┘

trimRight

Introduced in: v20.1

Removes the specified characters from the end of a string. By default, removes common whitespace (ASCII) characters.

Syntax

trimRight(s[, trim_characters])

Arguments

s — String to trim. String
trim_characters — Optional characters to trim. If not specified, common whitespace characters are removed. String

Returned value

Returns the string with specified characters trimmed from the right. String

Examples

Usage example

SELECT trimRight('ClickHouse','House');

┌─trimRight('C⋯', 'House')─┐
│ Click                    │
└──────────────────────────┘

tryBase32Decode

Introduced in: v25.6

Accepts a string and decodes it using Base32 encoding scheme.

Syntax

tryBase32Decode(encoded)

Arguments

encoded — String column or constant to decode. If the string is not valid Base32-encoded, returns an empty string in case of error. String

Returned value

Returns a string containing the decoded value of the argument. String

Examples

Usage example

SELECT tryBase32Decode('IVXGG33EMVSA====');

┌─tryBase32Decode('IVXGG33EMVSA====')─┐
│ Encoded                             │
└─────────────────────────────────────┘

tryBase58Decode

Introduced in: v22.10

Like base58Decode, but returns an empty string in case of error.

Syntax

tryBase58Decode(encoded)

Arguments

encoded — String column or constant. If the string is not valid Base58-encoded, returns an empty string in case of error. String

Returned value

Returns a string containing the decoded value of the argument. String

Examples

Usage example

SELECT tryBase58Decode('3dc8KtHrwM') AS res, tryBase58Decode('invalid') AS res_invalid;

┌─res─────┬─res_invalid─┐
│ Encoded │             │
└─────────┴─────────────┘

tryBase64Decode

Introduced in: v18.16

Like base64Decode, but returns an empty string in case of error.

Syntax

tryBase64Decode(encoded)

Arguments

encoded — String column or constant to decode. If the string is not valid Base64-encoded, returns an empty string in case of error. String

Returned value

Returns a string containing the decoded value of the argument. String

Examples

Usage example

SELECT tryBase64Decode('Y2xpY2tob3VzZQ==')

┌─tryBase64Decode('Y2xpY2tob3VzZQ==')─┐
│ clickhouse                          │
└─────────────────────────────────────┘

tryBase64URLDecode

Introduced in: v18.16

Like base64URLDecode, but returns an empty string in case of error.

Syntax

tryBase64URLDecode(encoded)

Arguments

encoded — String column or constant to decode. If the string is not valid Base64-encoded, returns an empty string in case of error. String

Returned value

Returns a string containing the decoded value of the argument. String

Examples

Usage example

SELECT tryBase64URLDecode('aHR0cHM6Ly9jbGlja2hvdXNlLmNvbQ')

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

tryIdnaEncode

Introduced in: v24.1

Syntax

tryIdnaEncode(s)

Arguments

s — Input string. String

Returned value

Returns an ASCII representation of the input string according to the IDNA mechanism of the input value, or empty string if input is invalid. String

Examples

Usage example

SELECT tryIdnaEncode('straße.münchen.de')

┌─tryIdnaEncode('straße.münchen.de')──┐
│ xn--strae-oqa.xn--mnchen-3ya.de     │
└─────────────────────────────────────┘

tryPunycodeDecode

Introduced in: v24.1

Like punycodeDecode but returns an empty string if no valid Punycode-encoded string is given.

Syntax

tryPunycodeDecode(s)

Arguments

s — Punycode-encoded string. String

Returned value

Returns the plaintext of the input value, or empty string if input is invalid. String

Examples

Usage example

SELECT tryPunycodeDecode('Mnchen-3ya')

┌─tryPunycodeDecode('Mnchen-3ya')─┐
│ München                         │
└─────────────────────────────────┘

upper

Introduced in: v1.1

Converts the ASCII Latin symbols in a string to uppercase.

Syntax

upper(s)

Arguments

s — The string to convert to uppercase. String

Returned value

Returns an uppercase string from s. String

Examples

Usage example

SELECT upper('clickhouse')

┌─upper('clickhouse')─┐
│ CLICKHOUSE          │
└─────────────────────┘

upperUTF8

Introduced in: v1.1

Converts a string to uppercase, assuming that the string contains valid UTF-8 encoded text. If this assumption is violated, no exception is thrown and the result is undefined.

Note

This function doesn't detect the language, e.g. for Turkish the result might not be exactly correct (i/İ vs. i/I). If the length of the UTF-8 byte sequence is different for upper and lower case of a code point (such as ẞ and ß), the result may be incorrect for that code point.

Syntax

upperUTF8(s)

Arguments

s — A string type. String

Returned value

A String data type value. String

Examples

Usage example

SELECT upperUTF8('München') AS Upperutf8

┌─Upperutf8─┐
│ MÜNCHEN   │
└───────────┘

empty​

notEmpty​

length​

lengthUTF8​

left​

leftUTF8​

leftPad​

leftPadUTF8​

right​

rightUTF8​

rightPad​

rightPadUTF8​

compareSubstrings​

lower​

upper​

lowerUTF8​

upperUTF8​

isValidUTF8​

toValidUTF8​

repeat​

space​

reverse​

reverseUTF8​

concat​

concatAssumeInjective​

concatWithSeparator​

concatWithSeparatorAssumeInjective​

substring​

substringUTF8​

substringIndex​

substringIndexUTF8​

appendTrailingCharIfAbsent​

convertCharset​

base32Encode​

base32Decode​

tryBase32Decode​

base58Encode​

base58Decode​

tryBase58Decode​

base64Encode​

base64URLEncode​

base64Decode​

base64URLDecode​

tryBase64Decode​

tryBase64URLDecode​

endsWith​

endsWithUTF8​

startsWith​

startsWithUTF8​

trim​

trimLeft​

trimRight​

trimBoth​

CRC32​

CRC32IEEE​

CRC64​

normalizeUTF8NFC​

normalizeUTF8NFD​

normalizeUTF8NFKC​

normalizeUTF8NFKD​

encodeXMLComponent​

decodeXMLComponent​

decodeHTMLComponent​

extractTextFromHTML​

ascii​

soundex​

punycodeEncode​

punycodeDecode​

tryPunycodeDecode​

idnaEncode​

tryIdnaEncode​

idnaDecode​

byteHammingDistance​

stringJaccardIndex​

stringJaccardIndexUTF8​

editDistance​

editDistanceUTF8​

damerauLevenshteinDistance​

jaroSimilarity​

jaroWinklerSimilarity​

empty

notEmpty

length

lengthUTF8

left

leftUTF8

leftPad

leftPadUTF8

right

rightUTF8

rightPad

rightPadUTF8

compareSubstrings

lower

upper

lowerUTF8

upperUTF8

isValidUTF8

toValidUTF8

repeat

space

reverse

reverseUTF8

concat

concatAssumeInjective

concatWithSeparator

concatWithSeparatorAssumeInjective

substring

substringUTF8

substringIndex

substringIndexUTF8

appendTrailingCharIfAbsent

convertCharset

base32Encode

base32Decode

tryBase32Decode

base58Encode

base58Decode

tryBase58Decode

base64Encode

base64URLEncode

base64Decode

base64URLDecode

tryBase64Decode

tryBase64URLDecode

endsWith

endsWithUTF8

startsWith

startsWithUTF8

trim

trimLeft

trimRight

trimBoth

CRC32

CRC32IEEE

CRC64

normalizeUTF8NFC

normalizeUTF8NFD

normalizeUTF8NFKC

normalizeUTF8NFKD

encodeXMLComponent

decodeXMLComponent

decodeHTMLComponent

extractTextFromHTML

ascii

soundex

punycodeEncode

punycodeDecode

tryPunycodeDecode

idnaEncode

tryIdnaEncode

idnaDecode

byteHammingDistance

stringJaccardIndex

stringJaccardIndexUTF8

editDistance

editDistanceUTF8

damerauLevenshteinDistance

jaroSimilarity

jaroWinklerSimilarity