Skip to main content

Documentation Index

Fetch the complete documentation index at: https://private-7c7dfe99-page-updates.mintlify.app/llms.txt

Use this file to discover all available pages before exploring further.

General strings functions and functions for searching in strings are described separately.
The documentation below is generated from the system.functions system table.

​
format

Introduced in: v20.1.0 Format the pattern string with the values (strings, integers, etc.) listed in the arguments, similar to formatting in Python. The pattern string can contain replacement fields surrounded by curly braces {}. Anything not contained in braces is considered literal text and copied verbatim into the output. Literal brace character can be escaped by two braces: {{ and }}. Field names can be numbers (starting from zero) or empty (then they are implicitly given monotonically increasing numbers). Syntax 
format(pattern, s0[, s1, ...])
Arguments
  • pattern — The format string containing placeholders. String
  • s0[, s1, ...] — One or more values to substitute into the pattern. Any
Returned value Returns a formatted string. String Examples Numbered placeholders
Query
SELECT format('{1} {0} {1}', 'World', 'Hello')
Response
┌─format('{1} {0} {1}', 'World', 'Hello')─┐
│ Hello World Hello                       │
└─────────────────────────────────────────┘
Implicit numbering
Query
SELECT format('{} {}', 'Hello', 'World')
Response
┌─format('{} {}', 'Hello', 'World')─┐
│ Hello World                       │
└───────────────────────────────────┘

​
overlay

Introduced in: v24.9.0 Replaces part of the string input with another string replace, starting at the 1-based index offset. Syntax 
overlay(s, replace, offset[, length])
Arguments
  • s — The input string. String
  • replace — The replacement string const String
  • offset — An integer type Int (1-based). If offset is negative, it is counted from the end of the string s. Int
  • length — Optional. An integer type Int. length specifies the length of the snippet within the input string s to be replaced. If length is not specified, the number of bytes removed from s equals the length of replace; otherwise length bytes are removed. Int
Returned value Returns a string with replacement. String Examples Basic replacement
Query
SELECT overlay('My father is from Mexico.', 'mother', 4) AS res;
Response
┌─res──────────────────────┐
│ My mother is from Mexico.│
└──────────────────────────┘
Replacement with length
Query
SELECT overlay('My father is from Mexico.', 'dad', 4, 6) AS res;
Response
┌─res───────────────────┐
│ My dad is from Mexico.│
└───────────────────────┘

​
overlayUTF8

Introduced in: v24.9.0 Replace part of the string s with another string replace, starting at the 1-based index offset. 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 
overlayUTF8(s, replace, offset[, length])
Arguments
  • s — The input string. String
  • replace — The replacement string. const String
  • offset — An integer type Int (1-based). If offset is negative, it is counted from the end of the input string s. (U)Int*
  • length — Optional. Specifies the length of the snippet within the input string s to be replaced. If length is not specified, the number of characters removed from s equals the length of replace, otherwise length characters are removed. (U)Int*
Returned value Returns a string with replacement. String Examples UTF-8 replacement
Query
SELECT overlayUTF8('Mein Vater ist aus Österreich.', 'der Türkei', 20) AS res;
Response
┌─res───────────────────────────┐
│ Mein Vater ist aus der Türkei.│
└───────────────────────────────┘

​
printf

Introduced in: v24.8.0 The printf function formats the given string with the values (strings, integers, floating-points etc.) listed in the arguments, similar to printf function in C++. The format string can contain format specifiers starting with % character. Anything not contained in % and the following format specifier is considered literal text and copied verbatim into the output. Literal % character can be escaped by %%. The format string can be either a constant or a column expression, allowing different format patterns per row. Syntax 
printf(format[, sub1, sub2, ...])
Arguments
  • format — The format string with % specifiers. String
  • sub1, sub2, ... — Optional. Zero or more values to substitute into the format string. Any
Returned value Returns a formatted string. String Examples C++-style formatting
Query
SELECT printf('%%%s %s %d', 'Hello', 'World', 2024);
Response
┌─printf('%%%s %s %d', 'Hello', 'World', 2024)─┐
│ %Hello World 2024                            │
└──────────────────────────────────────────────┘

​
regexpQuoteMeta

Introduced in: v20.1.0 Adds a backslash before these characters with special meaning in regular expressions: \0, \\, |, (, ), ^, $, ., [, ], ?, *, +, {, :, -. This implementation slightly differs from re2::RE2::QuoteMeta. It escapes zero byte as \0 instead of \x00 and it escapes only required characters. Syntax 
regexpQuoteMeta(s)
Arguments
  • s — The input string containing characters to be escaped for regex. String
Returned value Returns a string with regex special characters escaped. String Examples Escape regex special characters
Query
SELECT regexpQuoteMeta('Hello. [World]? (Yes)*') AS res
Response
┌─res───────────────────────────┐
│ Hello\. \[World\]\? \(Yes\)\* │
└───────────────────────────────┘

​
replaceAll

Introduced in: v1.1.0 Replaces all occurrences of the substring pattern in haystack by the replacement string. Syntax 
replaceAll(haystack, pattern, replacement)
Aliases: replace Arguments
  • haystack — The input string to search in. String
  • pattern — The substring to find and replace. const String
  • replacement — The string to replace the pattern with. const String
Returned value Returns a string with all occurrences of pattern replaced. String Examples Replace all occurrences
Query
SELECT replaceAll('Hello, Hello world', 'Hello', 'Hi') AS res;
Response
┌─res──────────┐
│ Hi, Hi world │
└──────────────┘

​
replaceOne

Introduced in: v1.1.0 Replaces the first occurrence of the substring pattern in haystack by the replacement string. Syntax 
replaceOne(haystack, pattern, replacement)
Arguments
  • haystack — The input string to search in. String
  • pattern — The substring to find and replace. const String
  • replacement — The string to replace the pattern with. const String
Returned value Returns a string with the first occurrence of pattern replaced. String Examples Replace first occurrence
Query
SELECT replaceOne('Hello, Hello world', 'Hello', 'Hi') AS res;
Response
┌─res─────────────┐
│ Hi, Hello world │
└─────────────────┘

​
replaceRegexpAll

Introduced in: v1.1.0 Like replaceRegexpOne but replaces all occurrences of the pattern. As an exception, if a regular expression worked on an empty substring, the replacement is not made more than once. Syntax 
replaceRegexpAll(haystack, pattern, replacement)
Aliases: REGEXP_REPLACE Arguments
  • haystack — The input string to search in. String
  • pattern — The regular expression pattern to find. const String
  • replacement — The string to replace the pattern with, may contain substitutions. const String
Returned value Returns a string with all regex matches replaced. String Examples Replace all characters with doubled version
Query
SELECT replaceRegexpAll('Hello123', '.', '\\\\0\\\\0') AS res
Response
┌─res──────────────────┐
│ HHeelllloo112233     │
└──────────────────────┘
Empty substring replacement example
Query
SELECT replaceRegexpAll('Hello, World!', '^', 'here: ') AS res
Response
┌─res─────────────────┐
│ here: Hello, World! │
└─────────────────────┘

​
replaceRegexpOne

Introduced in: v1.1.0 Replaces the first occurrence of the substring matching the regular expression pattern (in re2 syntax) in haystack by the replacement string. replacement can contain substitutions \0-\9. Substitutions \1-\9 correspond to the 1st to 9th capturing group (submatch), substitution \0 corresponds to the entire match. To use a verbatim \ character in the pattern or replacement strings, escape it using \. Also keep in mind that string literals require extra escaping. Syntax 
replaceRegexpOne(haystack, pattern, replacement)
Arguments
  • haystack — The input string to search in. String
  • pattern — The regular expression pattern to find. const String
  • replacement — The string to replace the pattern with, may contain substitutions. const String
Returned value Returns a string with the first regex match replaced. String Examples Converting ISO dates to American format
Query
SELECT DISTINCT
    EventDate,
    replaceRegexpOne(toString(EventDate), '(\\d{4})-(\\d{2})-(\\d{2})', '\\2/\\3/\\1') AS res
FROM test.hits
LIMIT 7
FORMAT TabSeparated
Response
2014-03-17      03/17/2014
2014-03-18      03/18/2014
2014-03-19      03/19/2014
2014-03-20      03/20/2014
2014-03-21      03/21/2014
2014-03-22      03/22/2014
2014-03-23      03/23/2014
Copying a string ten times
Query
SELECT replaceRegexpOne('Hello, World!', '.*', '\\\\0\\\\0\\\\0\\\\0\\\\0\\\\0\\\\0\\\\0\\\\0\\\\0') AS res
Response
┌─res────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World! │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

​
translate

Introduced in: v22.7.0 Replaces characters in the string s using a one-to-one character mapping defined by from and to strings. from and to must be constant ASCII strings. If from and to have equal sizes, each occurrence of the first character of first in s is replaced by the first character of to, the second character of first in s is replaced by the second character of to, etc. If from contains more characters than to, all occurrences of the characters at the end of from that have no corresponding character in to are deleted from s. Non-ASCII characters in s are not modified by the function. Syntax 
translate(s, from, to)
Arguments
  • s — The input string to translate. String
  • from — A constant ASCII string containing characters to replace. const String
  • to — A constant ASCII string containing replacement characters. const String
Returned value Returns a string with character translations applied. String Examples Character mapping
Query
SELECT translate('Hello, World!', 'delor', 'DELOR') AS res
Response
┌─res───────────┐
│ HELLO, WORLD! │
└───────────────┘
Different lengths
Query
SELECT translate('clickhouse', 'clickhouse', 'CLICK') AS res
Response
┌─res───┐
│ CLICK │
└───────┘

​
translateUTF8

Introduced in: v22.7.0 Like translate but assumes s, from and to are UTF-8 encoded strings. Syntax 
translateUTF8(s, from, to)
Arguments
  • s — UTF-8 input string to translate. String
  • from — A constant UTF-8 string containing characters to replace. const String
  • to — A constant UTF-8 string containing replacement characters. const String
Returned value Returns a String data type value. String Examples UTF-8 character translation
Query
SELECT translateUTF8('Münchener Straße', 'üß', 'us') AS res;
Response
┌─res──────────────┐
│ Munchener Strase │
└──────────────────┘