REPLACE_EMPTY
Description
The REPLACE_EMPTY function is used to replace a part of characters in a string with other characters. Unlike the REPLACE function, when old is an empty string, the new string is inserted before each character of the str string and at the end of the str string.
This function is mainly used to be compatible with Presto and Trino, and its behavior is exactly the same as the REPLACE() function in Presto and Trino. Supported since version 2.1.5.
Syntax
REPLACE_EMPTY ( <str>, <old>, <new> )
Parameters
| Parameter | Description |
|---|---|
<str> | The string that needs to be replaced. |
<old> | The substring that needs to be replaced. If <old> is not in <str>, no replacement will be performed. If <old> is an empty string, the <new> string will be inserted before each character of the str string. |
<new> | The new substring used to replace <old>. |
Return Value
Returns VARCHAR — the new string after replacing the substring. Special cases:
- If any parameter is NULL, returns NULL.
- If
<old>is an empty string, returns the string with<new>inserted before every character of<str>and at the end. - If
<old>is not found in<str>, returns<str>unchanged.
Examples
For reference, REPLACE rewrites every occurrence of <old> and leaves the rest of the string untouched:
SELECT replace('hello world', 'world', 'universe');
+---------------------------------------------+
| replace('hello world', 'world', 'universe') |
+---------------------------------------------+
| hello universe |
+---------------------------------------------+
REPLACE_EMPTY behaves the same in the ordinary case (see example 2 below), but differs when <old> is an empty string — it then inserts <new> before every character of <str> and at the end (example 1). The remaining examples cover the corner cases.
- Basic usage: insert behavior when
<old>is an empty string.
SELECT replace_empty('abc', '', 'x');
+-------------------------------+
| replace_empty('abc', '', 'x') |
+-------------------------------+
| xaxbxcx |
+-------------------------------+
- Plain substring replacement (same behavior as
REPLACE).
SELECT replace_empty('hello', 'l', 'L');
+----------------------------------+
| replace_empty('hello', 'l', 'L') |
+----------------------------------+
| heLLo |
+----------------------------------+
- Empty
<str>together with empty<old>.
SELECT replace_empty('', '', 'x');
+----------------------------+
| replace_empty('', '', 'x') |
+----------------------------+
| x |
+----------------------------+
- NULL propagation.
SELECT replace_empty(NULL, 'old', 'new');
+-----------------------------------+
| replace_empty(NULL, 'old', 'new') |
+-----------------------------------+
| NULL |
+-----------------------------------+
- Multi-byte (UTF-8)
<new>.
SELECT replace_empty('hello', 'l', 'ṭṛìṭ');
+--------------------------------------------+
| replace_empty('hello', 'l', 'ṭṛìṭ') |
+--------------------------------------------+
| heṭṛìṭṭṛìṭo |
+--------------------------------------------+
