--- title: Replace Functions reference meta: description: Functions for replacing in strings. headingMaxLevels: 2 --- # Functions for replacing in strings General strings functions and functions for searching in strings are described separately. See [String search functions](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 ```sql overlay(s, replace, offset[, length]) ``` ### Arguments - `s`: [String](../data-types/string). The original string. - `replace`: [String](../data-types/string). The string to insert. - `offset`: [Int](../data-types/int-uint). The 1-based starting position for the replacement. A negative offset counts from the end of the string. - `length`: Optional. [Int](../data-types/int-uint). The number of bytes in `s` to replace. If omitted, the length of `replace` is used. ### Returns A [String](../data-types/string) with the specified segment replaced. ### Example ```sql SELECT overlay('My father is from Mexico.', 'mother', 4) AS res ``` Result: ```text ┌─res──────────────────────┐ │ My mother is from Mexico.│ └──────────────────────────┘ ``` ```sql SELECT overlay('My father is from Mexico.', 'dad', 4, 6) AS res ``` Result: ```text ┌─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 ```sql overlayUTF8(s, replace, offset[, length]) ``` ### Arguments - `s`: [String](../data-types/string). The original UTF-8 string. - `replace`: [String](../data-types/string). The UTF-8 string to insert. - `offset`: [Int](../data-types/int-uint). The 1-based starting character position for the replacement. A negative offset counts from the end of the string. - `length`: Optional. [Int](../data-types/int-uint). The number of UTF-8 characters in `s` to replace. If omitted, the character length of `replace` is used. ### Returns A [String](../data-types/string) with the specified segment replaced, preserving UTF-8 encoding. ### Example ```sql SELECT overlay('Mein Vater ist aus Österreich.', 'der Türkei', 20) AS res ``` Result: ```text ┌─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 ```sql replaceOne(haystack, pattern, replacement) ``` ### Arguments - `haystack`: [String](../data-types/string). The input string to search within. - `pattern`: [String](../data-types/string). The substring to find and replace. - `replacement`: [String](../data-types/string). The string to insert in place of the `pattern`. ### Returns A [String](../data-types/string) with the first occurrence of the `pattern` replaced. ### Example ```sql SELECT replaceOne('apple, banana, apple', 'apple', 'orange') AS res ``` Result: ```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 ```sql replaceAll(haystack, pattern, replacement) ``` ### Arguments - `haystack`: [String](../data-types/string). The input string to search within. - `pattern`: [String](../data-types/string). The substring to find and replace. - `replacement`: [String](../data-types/string). The string to insert in place of the `pattern`. ### Returns A [String](../data-types/string) with all occurrences of the `pattern` replaced. ### Example ```sql SELECT replaceAll('apple, banana, apple', 'apple', 'orange') AS res ``` Result: ```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 ```sql replaceRegexpOne(haystack, pattern, replacement) ``` ### Arguments - `haystack`: [String](../data-types/string). The input string to search within. - `pattern`: [String](../data-types/string). The regular expression (RE2 syntax) to match. - `replacement`: [String](../data-types/string). The string to insert. Can contain `\0` for the entire match or `\1` through `\9` for capturing groups. ### Returns A [String](../data-types/string) with the first regex match replaced. ### Example Converting ISO dates to American format: ```sql SELECT replaceRegexpOne('2023-10-26', '(\\d{4})-(\\d{2})-(\\d{2})', '\\2/\\3/\\1') AS res ``` Result: ```text ┌─res───────┐ │ 10/26/2023│ └───────────┘ ``` Copying a string ten times: ```sql SELECT replaceRegexpOne('Hello, World!', '.*', '\\0\\0\\0\\0\\0\\0\\0\\0\\0\\0') AS res ``` Result: ```text ┌─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 ```sql replaceRegexpAll(haystack, pattern, replacement) ``` ### Arguments - `haystack`: [String](../data-types/string). The input string to search within. - `pattern`: [String](../data-types/string). The regular expression (RE2 syntax) to match. - `replacement`: [String](../data-types/string). The string to insert. Can contain `\0` for the entire match or `\1` through `\9` for capturing groups. ### Returns A [String](../data-types/string) with all regex matches replaced. ### Example ```sql SELECT replaceRegexpAll('Hello, World!', '.', '\\0\\0') AS res ``` Result: ```text ┌─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.: ```sql SELECT replaceRegexpAll('Hello, World!', '^', 'here: ') AS res ``` Result: ```text ┌─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 ```sql regexpQuoteMeta(s) ``` ### Arguments - `s`: [String](../data-types/string). The input string containing characters to be escaped. ### Returns A [String](../data-types/string) with special regular expression characters escaped. ### Example ```sql SELECT regexpQuoteMeta('my.domain?com') AS res ``` Result: ```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 ```sql format(pattern, s0, s1, ...) ``` ### Arguments - `pattern`: [String](../data-types/string). The format string containing placeholders. - `s0, s1, ...`: Any type. The values to be inserted into the `pattern`. ### Returns A [String](../data-types/string) with the arguments formatted into the pattern. ### Example ```sql SELECT format('{1} {0} {1}', 'World', 'Hello') ``` ```result ┌─format('{1} {0} {1}', 'World', 'Hello')─┐ │ Hello World Hello │ └─────────────────────────────────────────┘ ``` With implicit numbers: ```sql SELECT format('{} {}', 'Hello', 'World') ``` ```result ┌─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 ```sql translate(s, from, to) ``` ### Arguments - `s`: [String](../data-types/string). The input string. - `from`: [String](../data-types/string). A string containing characters to be replaced. - `to`: [String](../data-types/string). A string containing replacement characters. ### Returns A [String](../data-types/string) with characters replaced or deleted according to the mapping. ### Example ```sql SELECT translate('Hello, World!', 'delor', 'DELOR') AS res ``` Result: ```text ┌─res───────────┐ │ HELLO, WORLD! │ └───────────────┘ ``` `from` and `to` arguments have different lengths: ```sql SELECT translate('clickhouse', 'clickhouse', 'CLICK') AS res ``` Result: ```text ┌─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 ```sql translateUTF8(s, from, to) ``` ### Arguments - `s`: [String](../data-types/string). The input UTF-8 string. - `from`: [String](../data-types/string). A UTF-8 string containing characters to be replaced. - `to`: [String](../data-types/string). A UTF-8 string containing replacement characters. ### Returns A [String](../data-types/string) with UTF-8 characters replaced or deleted. ### Example ```sql SELECT translateUTF8('Münchener Straße', 'üß', 'us') AS res ``` Result: ```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 ```sql printf(format, arg1, arg2, ...) ``` ### Arguments - `format`: [String](../data-types/string). The format string containing specifiers. - `arg1, arg2, ...`: Any type. The values to be formatted into the string. ### Returns A [String](../data-types/string) with the arguments formatted according to the specifiers. ### Example ```sql SELECT printf('%%%s %s %d', 'Hello', 'World', 2024) AS res ``` Result: ```result ┌─res───────────────┐ │ %Hello World 2024 │ └───────────────────┘ ```