Functions for Replacing in Strings

General strings functions and functions for searching in strings are described separately.

overlay

Replace part of the string input with another string replace, starting at the 1-based index offset.

Syntax

overlay(s, replace, offset[, length])

Parameters

s: A string type String.
replace: A string type String.
offset: An integer type Int (1-based). If offset is negative, it is counted from the end of the string s.
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.

Returned value

A String data type value.

Example

SELECT overlay('My father is from Mexico.', 'mother', 4) AS res;

Result:

┌─res──────────────────────┐
│ My mother is from Mexico.│
└──────────────────────────┘

SELECT overlay('My father is from Mexico.', 'dad', 4, 6) AS res;

Result:

┌─res───────────────────┐
│ My dad is from Mexico.│
└───────────────────────┘

overlayUTF8

Replace part of the string input 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])

Parameters

s: A string type String.
replace: A string type String.
offset: An integer type Int (1-based). If offset is negative, it is counted from the end of the input string s.
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 characters removed from s equals the length of replace; otherwise length characters are removed.

Returned value

A String data type value.

Example

SELECT overlay('Mein Vater ist aus Österreich.', 'der Türkei', 20) AS res;

Result:

┌─res───────────────────────────┐
│ Mein Vater ist aus der Türkei.│
└───────────────────────────────┘

replaceOne

Replaces the first occurrence of the substring pattern in haystack by the replacement string.

Syntax

replaceOne(haystack, pattern, replacement)

replaceAll

Replaces all occurrences of the substring pattern in haystack by the replacement string.

Syntax

replaceAll(haystack, pattern, replacement)

Alias: replace.

replaceRegexpOne

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)

Example

Converting ISO dates to American format:

SELECT DISTINCT
    EventDate,
    replaceRegexpOne(toString(EventDate), '(\\d{4})-(\\d{2})-(\\d{2})', '\\2/\\3/\\1') AS res
FROM test.hits
LIMIT 7
FORMAT TabSeparated

Result:

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:

SELECT replaceRegexpOne('Hello, World!', '.*', '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0') AS res

Result:

┌─res────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
│ Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World!Hello, World! │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

replaceRegexpAll

Like replaceRegexpOne but replaces all occurrences of the pattern.

Alias: REGEXP_REPLACE.

Example

SELECT replaceRegexpAll('Hello, World!', '.', '\\0\\0') AS res

Result:

┌─res────────────────────────┐
│ HHeelllloo,,  WWoorrlldd!! │
└────────────────────────────┘

As an exception, if a regular expression worked on an empty substring, the replacement is not made more than once, e.g.:

SELECT replaceRegexpAll('Hello, World!', '^', 'here: ') AS res

Result:

┌─res─────────────────┐
│ here: Hello, World! │
└─────────────────────┘

regexpQuoteMeta

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. For more information, see RE2

Syntax

regexpQuoteMeta(s)

format

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, ...)

Example

SELECT format('{1} {0} {1}', 'World', 'Hello')

┌─format('{1} {0} {1}', 'World', 'Hello')─┐
│ Hello World Hello                       │
└─────────────────────────────────────────┘

With implicit numbers:

SELECT format('{} {}', 'Hello', 'World')

┌─format('{} {}', 'Hello', 'World')─┐
│ Hello World                       │
└───────────────────────────────────┘

translate

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 1st character of first in s is replaced by the 1st character of to, the 2nd character of first in s is replaced by the 2nd 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)

Example

SELECT translate('Hello, World!', 'delor', 'DELOR') AS res

Result:

┌─res───────────┐
│ HELLO, WORLD! │
└───────────────┘

from and to arguments have different lengths:

SELECT translate('clickhouse', 'clickhouse', 'CLICK') AS res

Result:

┌─res───┐
│ CLICK │
└───────┘

translateUTF8

Like translate but assumes s, from and to are UTF-8 encoded strings.

Syntax

translateUTF8(s, from, to)

Parameters

s: A string type String.
from: A string type String.
to: A string type String.

Returned value

A String data type value.

Examples

Query:

SELECT translateUTF8('Münchener Straße', 'üß', 'us') AS res;

┌─res──────────────┐
│ Munchener Strase │
└──────────────────┘

printf

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 %%.

Syntax

printf(format, arg1, arg2, ...)

Example

Query:

SELECT printf('%%%s %s %d', 'Hello', 'World', 2024);

┌─printf('%%%s %s %d', 'Hello', 'World', 2024)─┐
│ %Hello World 2024                            │
└──────────────────────────────────────────────┘

format

Introduced in: v20.1

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

SELECT format('{1} {0} {1}', 'World', 'Hello')

┌─format('{1} {0} {1}', 'World', 'Hello')─┐
│ Hello World Hello                       │
└─────────────────────────────────────────┘

Implicit numbering

SELECT format('{} {}', 'Hello', 'World')

┌─format('{} {}', 'Hello', 'World')─┐
│ Hello World                       │
└───────────────────────────────────┘

overlay

Introduced in: v24.9

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

SELECT overlay('My father is from Mexico.', 'mother', 4) AS res;

┌─res──────────────────────┐
│ My mother is from Mexico.│
└──────────────────────────┘

Replacement with length

SELECT overlay('My father is from Mexico.', 'dad', 4, 6) AS res;

┌─res───────────────────┐
│ My dad is from Mexico.│
└───────────────────────┘

overlayUTF8

Introduced in: v24.9

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

SELECT overlayUTF8('Mein Vater ist aus Österreich.', 'der Türkei', 20) AS res;

┌─res───────────────────────────┐
│ Mein Vater ist aus der Türkei.│
└───────────────────────────────┘

printf

Introduced in: v24.8

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

SELECT printf('%%%s %s %d', 'Hello', 'World', 2024);

┌─printf('%%%s %s %d', 'Hello', 'World', 2024)─┐
│ %Hello World 2024                            │
└──────────────────────────────────────────────┘

regexpQuoteMeta

Introduced in: v20.1

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

SELECT regexpQuoteMeta('Hello. [World]? (Yes)*') AS res

┌─res───────────────────────────┐
│ Hello\. \[World\]\? \(Yes\)\* │
└───────────────────────────────┘

replaceAll

Introduced in: v1.1

Replaces all occurrences of the substring pattern in haystack by the replacement string.

Syntax

replaceAll(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 all occurrences of pattern replaced. String

Examples

Replace all occurrences

SELECT replaceAll('Hello, Hello world', 'Hello', 'Hi') AS res;

┌─res──────────┐
│ Hi, Hi world │
└──────────────┘

replaceOne

Introduced in: v1.1

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

SELECT replaceOne('Hello, Hello world', 'Hello', 'Hi') AS res;

┌─res─────────────┐
│ Hi, Hello world │
└─────────────────┘

replaceRegexpAll

Introduced in: v1.1

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)

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

SELECT replaceRegexpAll('Hello123', '.', '\\\\0\\\\0') AS res

┌─res──────────────────┐
│ HHeelllloo112233     │
└──────────────────────┘

Empty substring replacement example

SELECT replaceRegexpAll('Hello, World!', '^', 'here: ') AS res

┌─res─────────────────┐
│ here: Hello, World! │
└─────────────────────┘

replaceRegexpOne

Introduced in: v1.1

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

SELECT DISTINCT
    EventDate,
    replaceRegexpOne(toString(EventDate), '(\\d{4})-(\\d{2})-(\\d{2})', '\\2/\\3/\\1') AS res
FROM test.hits
LIMIT 7
FORMAT TabSeparated

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

SELECT replaceRegexpOne('Hello, World!', '.*', '\\\\0\\\\0\\\\0\\\\0\\\\0\\\\0\\\\0\\\\0\\\\0\\\\0') AS res

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

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

SELECT translate('Hello, World!', 'delor', 'DELOR') AS res

┌─res───────────┐
│ HELLO, WORLD! │
└───────────────┘

Different lengths

SELECT translate('clickhouse', 'clickhouse', 'CLICK') AS res

┌─res───┐
│ CLICK │
└───────┘

translateUTF8

Introduced in: v22.7

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

SELECT translateUTF8('Münchener Straße', 'üß', 'us') AS res;

┌─res──────────────┐
│ Munchener Strase │
└──────────────────┘

overlay​

overlayUTF8​

replaceOne​

replaceAll​

replaceRegexpOne​

replaceRegexpAll​

regexpQuoteMeta​

format​

translate​

translateUTF8​

printf​

format​

overlay​

overlayUTF8​

printf​

regexpQuoteMeta​

replaceAll​

replaceOne​

replaceRegexpAll​

replaceRegexpOne​

translate​

translateUTF8​

overlay

overlayUTF8

replaceOne

replaceAll

replaceRegexpOne

replaceRegexpAll

regexpQuoteMeta

format

translate

translateUTF8

printf

format

overlay

overlayUTF8

printf

regexpQuoteMeta

replaceAll

replaceOne

replaceRegexpAll

replaceRegexpOne

translate

translateUTF8