Functions for replacing in strings¶

General strings functions and functions for searching in strings are described separately. See String search functions.

overlay¶

Replaces a segment of an input string with another string, starting at a specified 1-based offset. You can optionally define the length of the segment to be replaced.

Syntax¶

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

Arguments¶

s: String. The original string.
replace: String. The string to insert.
offset: Int. The 1-based starting position for the replacement. A negative offset counts from the end of the string.
length: Optional. Int. The number of bytes in s to replace. If omitted, the length of replace is used.

Returns¶

A String with the specified segment replaced.

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¶

Replaces a segment of an input string with another string, similar to overlay, but operates on UTF-8 characters rather than bytes. It assumes the input string is valid UTF-8.

Syntax¶

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

Arguments¶

s: String. The original UTF-8 string.
replace: String. The UTF-8 string to insert.
offset: Int. The 1-based starting character position for the replacement. A negative offset counts from the end of the string.
length: Optional. Int. The number of UTF-8 characters in s to replace. If omitted, the character length of replace is used.

Returns¶

A String with the specified segment replaced, preserving UTF-8 encoding.

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 only the first occurrence of a specified substring within a larger string with a new replacement string.

Syntax¶

replaceOne(haystack, pattern, replacement)

Arguments¶

haystack: String. The input string to search within.
pattern: String. The substring to find and replace.
replacement: String. The string to insert in place of the pattern.

Returns¶

A String with the first occurrence of the pattern replaced.

Example¶

SELECT replaceOne('apple, banana, apple', 'apple', 'orange') AS res

Result:

┌─res──────────────────┐
│ orange, banana, apple│
└──────────────────────┘

replaceAll¶

Replaces all occurrences of a specified substring within a larger string with a new replacement string.

Alias: replace.

Syntax¶

replaceAll(haystack, pattern, replacement)

Arguments¶

haystack: String. The input string to search within.
pattern: String. The substring to find and replace.
replacement: String. The string to insert in place of the pattern.

Returns¶

A String with all occurrences of the pattern replaced.

Example¶

SELECT replaceAll('apple, banana, apple', 'apple', 'orange') AS res

Result:

┌─res────────────────────┐
│ orange, banana, orange │
└────────────────────────┘

replaceRegexpOne¶

Replaces the first substring that matches a given regular expression pattern with a specified replacement string. The replacement string can include backreferences to capturing groups from the pattern.

Syntax¶

replaceRegexpOne(haystack, pattern, replacement)

Arguments¶

haystack: String. The input string to search within.
pattern: String. The regular expression (RE2 syntax) to match.
replacement: String. The string to insert. Can contain \0 for the entire match or \1 through \9 for capturing groups.

Returns¶

A String with the first regex match replaced.

Example¶

Converting ISO dates to American format:

SELECT replaceRegexpOne('2023-10-26', '(\\d{4})-(\\d{2})-(\\d{2})', '\\2/\\3/\\1') AS res

Result:

┌─res───────┐
│ 10/26/2023│
└───────────┘

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¶

Replaces all substrings that match a given regular expression pattern with a specified replacement string. This function behaves like replaceRegexpOne but applies the replacement globally.

Alias: REGEXP_REPLACE.

Syntax¶

replaceRegexpAll(haystack, pattern, replacement)

Arguments¶

haystack: String. The input string to search within.
pattern: String. The regular expression (RE2 syntax) to match.
replacement: String. The string to insert. Can contain \0 for the entire match or \1 through \9 for capturing groups.

Returns¶

A String with all regex matches replaced.

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 isn't made more than once, e.g.:

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

Result:

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

regexpQuoteMeta¶

Escapes special regular expression characters in an input string by adding a backslash before them. This is useful when you want to treat a literal string as a pattern in a regular expression without its special characters being interpreted.

Syntax¶

regexpQuoteMeta(s)

Arguments¶

s: String. The input string containing characters to be escaped.

Returns¶

A String with special regular expression characters escaped.

Example¶

SELECT regexpQuoteMeta('my.domain?com') AS res

Result:

┌─res────────────┐
│ my\.domain\?com│
└────────────────┘

format¶

Formats a pattern string by inserting provided arguments into placeholders. Placeholders are defined by curly braces {} and can be numbered (0-indexed) or empty for sequential insertion.

Syntax¶

format(pattern, s0, s1, ...)

Arguments¶

pattern: String. The format string containing placeholders.
s0, s1, ...: Any type. The values to be inserted into the pattern.

Returns¶

A String with the arguments formatted into the pattern.

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 an input string based on a one-to-one mapping defined by two other strings, from and to. Each character in the input string that matches a character in from is replaced by the character at the corresponding position in to. If from contains characters not present in to, those characters are deleted from the input string. This function operates on ASCII characters; non-ASCII characters are not modified.

Syntax¶

translate(s, from, to)

Arguments¶

s: String. The input string.
from: String. A string containing characters to be replaced.
to: String. A string containing replacement characters.

Returns¶

A String with characters replaced or deleted according to the mapping.

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¶

Performs character replacement similar to translate, but specifically designed for UTF-8 encoded strings. It assumes s, from, and to are valid UTF-8.

Syntax¶

translateUTF8(s, from, to)

Arguments¶

s: String. The input UTF-8 string.
from: String. A UTF-8 string containing characters to be replaced.
to: String. A UTF-8 string containing replacement characters.

Returns¶

A String with UTF-8 characters replaced or deleted.

Example¶

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

Result:

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

printf¶

Formats a string using C++ style printf format specifiers. The format string can contain specifiers like %s for strings, %d for integers, etc., which are replaced by the corresponding arguments. Literal % characters must be escaped as %%.

Syntax¶

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

Arguments¶

format: String. The format string containing specifiers.
arg1, arg2, ...: Any type. The values to be formatted into the string.

Returns¶

A String with the arguments formatted according to the specifiers.

Example¶

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

Result:

┌─res───────────────┐
│ %Hello World 2024 │
└───────────────────┘