Functions for Replacing in Strings
General strings functions and functions for searching in strings are described separately.
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 containing 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 of the same size. Non-ASCII characters in the original string are not modified.
Syntax
translate(s, from, to)
Example
SELECT translate('Hello, World!', 'delor', 'DELOR') AS res
Result:
┌─res───────────┐
│ HELLO, WORLD! │
└───────────────┘
translateUTF8
Like translate but assumes s
, from
and to
are UTF-8 encoded strings.
Syntax
translateUTF8(s, from, to)
Parameters
Returned value
- A String data type value.
Examples
Query:
SELECT translateUTF8('Münchener Straße', 'üß', 'us') AS res;
┌─res──────────────┐
│ Munchener Strase │
└──────────────────┘