REPLACE
Description
REPLACE
returns the strings with pattern
-substrings replaced by the string replace
.
It should be noted that not all occurrences of pattern
are replaced. Consider the following string: ABABA
. Replacing the pattern ABA
by X
will lead to the output XBA
although ABA
occurs twice. This is due to the fact, that the first replacement of ABA
will prevent the occurrence of the next. This behavior is identical to MSSQL's behavior.
Supported input column types: STRING
Output column type: STRING
Syntax
REPLACE ( table.column, pattern, replace )
table.column: The string column on which the
REPLACE
operator is executed.pattern: The pattern which is searched for in the input string.
replace: The replacement for a found pattern in the input string.
NULL handling
If any of the inputs (table.column
, pattern
or replace
) is NULL, the result will be NULL as well.
Examples
[1] Returns the strings with occurrences of | ||||||||||||||||||
| ||||||||||||||||||
|
[2] Having an empty pattern does not modify the input at all. | ||||||||
| ||||||||
|
[3] Having an empty replacement does delete the pattern. | ||||||||
| ||||||||
|
[4] Using the REPLACE operator with three columns as input instead of constants for the pattern and replacement. | ||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||
|