String

CRC32

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('RawTree')

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

Introduced in version 20.1.

CRC32IEEE

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

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

Introduced in version 20.1.

CRC64

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

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

Introduced in version 20.1.

appendTrailingCharIfAbsent

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

Introduced in version 1.1.

ascii

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

Introduced in version 22.11.

base32Decode

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

Introduced in version 25.6.

base32Encode

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

Introduced in version 25.6.

base58Decode

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

Introduced in version 22.7.

base58Encode

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

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

Introduced in version 22.7.

base64Decode

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==')─┐
│ rawtree                       │
└──────────────────────────────────┘

Introduced in version 18.16.

base64Encode

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('rawtree')

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

Introduced in version 18.16.

base64URLDecode

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

Introduced in version 24.6.

base64URLEncode

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

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

Introduced in version 18.16.

basename

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

Introduced in version 20.1.

byteHammingDistance

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

Introduced in version 23.9.

compareSubstrings

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

Introduced in version 25.2.

concat

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

Introduced in version 1.1.

concatAssumeInjective

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

Introduced in version 1.1.

concatWithSeparator

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

Introduced in version 22.12.

concatWithSeparatorAssumeInjective

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

Introduced in version 22.12.

conv

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)

1010

Convert hexadecimal to decimal

SELECT conv('FF', 16, 10)

255

Convert with negative number

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

FFFFFFFFFFFFFFFF

Convert binary to octal

SELECT conv('1010', 2, 8)

12

Introduced in version 1.1.

convertCharset

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

Introduced in version 1.1.

damerauLevenshteinDistance

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('rawtree', 'mouse')

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

Introduced in version 24.1.

decodeHTMLComponent

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('<div>Hello & "World"</div>')

┌─decodeHTMLComponent('<div>Hello & "World"</div>')─┐
│ <div>Hello & "World"</div>                                                  │
└─────────────────────────────────────────────────────────────────────────────┘

Introduced in version 23.9.

decodeXMLComponent

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('<tag>Hello & World</tag>')

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

Introduced in version 21.2.

editDistance

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('rawtree', 'mouse')

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

Introduced in version 23.9.

editDistanceUTF8

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

Introduced in version 24.6.

encodeXMLComponent

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

Introduced in version 21.1.

endsWith

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('RawTree', 'House');

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

Introduced in version 1.1.

endsWithCaseInsensitive

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('RawTree', 'HOUSE');

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

Introduced in version 25.9.

endsWithCaseInsensitiveUTF8

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

Introduced in version 25.9.

endsWithUTF8

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

Introduced in version 23.8.

extractTextFromHTML

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>
        
    </body>
</html>
');

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

Introduced in version 21.3.

firstLine

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

Introduced in version 23.7.

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

Introduced in version 24.1.

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

Introduced in version 24.1.

initcap

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

Introduced in version 23.7.

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(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('не тормозит')─┐
│ Не Тормозит                │
└────────────────────────────┘

Introduced in version 23.7.

isValidASCII

Returns 1 if the input String or FixedString contains only ASCII bytes (0x00–0x7F), otherwise 0. Optimized for the positive case (the input is valid ASCII).

Examples

isValidASCII

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

Introduced in version 25.9.

isValidUTF8

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

Introduced in version 20.1.

jaroSimilarity

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('rawtree', 'click')

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

Introduced in version 24.1.

jaroWinklerSimilarity

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('rawtree', 'click')

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

Introduced in version 24.1.

left

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)

Hello

Negative offset

SELECT left('Hello World', -6)

Hello

Introduced in version 22.1.

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

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

Introduced in version 21.8.

leftPadUTF8

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)─┐
│ ***абвг                     │    дежз                │
└─────────────────────────────┴────────────────────────┘

Introduced in version 21.8.

leftUTF8

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)

Пр

Introduced in version 22.1.

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.

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

Introduced in version 1.1.

lower

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('RAWTREE')

┌─lower('RAWTREE')─┐
│ rawtree          │
└─────────────────────┘

Introduced in version 1.1.

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.

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

Introduced in version 1.1.

normalizeUTF8NFC

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

Introduced in version 21.11.

normalizeUTF8NFD

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

Introduced in version 21.11.

normalizeUTF8NFKC

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

Introduced in version 21.11.

normalizeUTF8NFKD

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

Introduced in version 21.11.

punycodeDecode

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

Introduced in version 24.1.

punycodeEncode

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

Introduced in version 24.1.

regexpExtract

Extracts the first string in haystack that matches the regexp pattern and corresponds to the regex group index.

Syntax

regexpExtract(haystack, pattern[, index])

Arguments

• haystack — String, in which regexp pattern will be matched. String
• pattern — String, regexp expression. pattern may contain multiple regexp groups, index indicates which regex group to extract. An index of 0 means matching the entire regular expression. const String
• index — Optional. An integer number greater or equal 0 with default 1. It represents which regex group to extract. (U)Int*

Returned value

Returns a string match String

Examples

Usage example

SELECT
    regexpExtract('100-200', '(\\d+)-(\\d+)', 1),
    regexpExtract('100-200', '(\\d+)-(\\d+)', 2),
    regexpExtract('100-200', '(\\d+)-(\\d+)', 0),
    regexpExtract('100-200', '(\\d+)-(\\d+)');

┌─regexpExtract('100-200', '(\\d+)-(\\d+)', 1)─┬─regexpExtract('100-200', '(\\d+)-(\\d+)', 2)─┬─regexpExtract('100-200', '(\\d+)-(\\d+)', 0)─┬─regexpExtract('100-200', '(\\d+)-(\\d+)')─┐
│ 100                                          │ 200                                          │ 100-200                                      │ 100                                       │
└──────────────────────────────────────────────┴──────────────────────────────────────────────┴──────────────────────────────────────────────┴───────────────────────────────────────────┘

Introduced in version 23.2.

repeat

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

Introduced in version 20.1.

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.

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('RawTree')

esuoHkcilC

Introduced in version 1.1.

right

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

Introduced in version 22.1.

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

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

Introduced in version 21.8.

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. 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)─┐
│ абвг***                      │ абвг                    │
└──────────────────────────────┴─────────────────────────┘

Introduced in version 21.8.

rightUTF8

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)

ет

Introduced in version 22.1.

soundex

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

Introduced in version 23.4.

space

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

Introduced in version 23.5.

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, 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*
• min_cutoff_length — Optional. If specified, only n-grams with length greater or equal than min_cutoff_length are returned. The default value is the same as min_ngram_length. Should be not less than min_ngram_length and not greater than max_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']         │
└────────────────────────────────────┘

Introduced in version 25.5.

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, 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*
• min_cutoff_length — Optional. If specified, only n-grams with length greater or equal than min_cutoff_length are returned. The default value is the same as min_ngram_length. Should be not less than min_ngram_length and not greater than max_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]      │
└────────────────────────────────────────────────────┘

Introduced in version 25.5.

sparseGramsHashesUTF8

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*
• min_cutoff_length — Optional. If specified, only n-grams with length greater or equal than min_cutoff_length are returned. The default value is the same as min_ngram_length. Should be not less than min_ngram_length and not greater than max_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] │
└───────────────────────────────────┘

Introduced in version 25.5.

sparseGramsUTF8

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*
• min_cutoff_length — Optional. If specified, only n-grams with length greater or equal than min_cutoff_length are returned. The default value is the same as min_ngram_length. Should be not less than min_ngram_length and not greater than max_ngram_length. UInt*

Returned value

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

Examples

Usage example

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

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

Introduced in version 25.5.

startsWith

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('RawTree', 'Click');

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

Introduced in version 1.1.

startsWithCaseInsensitive

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('RawTree', 'CLICK');

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

Introduced in version 25.9.

startsWithCaseInsensitiveUTF8

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

Introduced in version 25.9.

startsWithUTF8

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

Introduced in version 23.8.

stringBytesEntropy

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

Introduced in version 25.6.

stringBytesUniq

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

Introduced in version 25.6.

stringJaccardIndex

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('rawtree', 'mouse')

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

Introduced in version 23.11.

stringJaccardIndexUTF8

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

Introduced in version 23.11.

substring

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

Introduced in version 1.1.

substringIndex

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.rawtree.com', '.', 2)

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

Introduced in version 23.7.

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

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

Introduced in version 23.7.

substringUTF8

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

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

An optional argument length specifies the maximum number of code points 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 code points, starting at code point 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

Introduced in version 1.1.

toValidUTF8

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

Introduced in version 20.1.

trimBoth

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('$$RawTree$$', '$')

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

Introduced in version 20.1.

trimLeft

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('RawTree', 'Click');

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

Introduced in version 20.1.

trimRight

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('RawTree','House');

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

Introduced in version 20.1.

tryBase32Decode

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

Introduced in version 25.6.

tryBase58Decode

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

Introduced in version 22.10.

tryBase64Decode

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==')─┐
│ rawtree                          │
└─────────────────────────────────────┘

Introduced in version 18.16.

tryBase64URLDecode

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

Introduced in version 18.16.

tryIdnaEncode

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 it returns an empty string instead of throwing an exception.

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

Introduced in version 24.1.

tryPunycodeDecode

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

Introduced in version 24.1.

upper

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('rawtree')

┌─upper('rawtree')─┐
│ RAWTREE          │
└─────────────────────┘

Introduced in version 1.1.

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

Introduced in version 1.1.