replace Function

The replace function returns the source with every occurrence of the search string replaced with the replacement string.

Syntax

returnvalue replace(source, search_string [, replacement_string])

source ::= any*
search_string ::= any*
replacement_string ::= any*
returnvalue ::= string

Semantics

source

The input string that should be searched. This argument is implicitly cast to a sequence of strings.

search_string

The string that should be searched in the source. This argument is implicitly cast to a sequence of strings.

replacement_string

The string that should be substitued in place of search_string in the source. This is an optional argument. If replacement_string is omitted or empty sequence, then all occurrences of search_string are removed from source. The result will be checked so that the result would not be bigger than STRING_MAX_SIZE = 2^18 - 1 in chars ie. 512kb, if that is the case a runtime query exception is thrown. This argument is implicitly cast to a sequence of strings.

returnvalue

Returns source if the search_string argument is NULL.

Returns NULL if source argument is NULL.

Returns NULL if either source or search_string argument is an empty sequence.

Returns NULL if any argument is a sequence with more than one item.

Example 12-20 replace Function

In this example, the string "e" is replaced with "X" in all the occurences in firstname. Notice the occurrence of "X" in Peter.

SELECT firstname, replace(firstname,"e","X") FROM users;

 +-----------+----------+
 | firstname | Column_2 |
 +-----------+----------+
 | John      | John     |
 | Peter     | PXtXr    |
 | Mary      | Mary     |
 +-----------+----------+

Example 12-21 replace Function

In this example, the string "ar" is replaced with "urph". Notice that in the source the remaining characters after the search_string are retained for output. This yields the output for "Mary" as "Murphy".

SELECT firstname, replace(firstname,"ar","urph") FROM users;

 +-----------+----------+
 | firstname | Column_2 |
 +-----------+----------+
 | John      | John     |
 | Peter     | Peter    |
 | Mary      | Murphy   |
 +-----------+----------+

Example 12-22 replace Function

In this example, the replacement_string is not specified. Since the replacement_string is not specified, the search_string is removed and the remaining source is displayed.

SELECT firstname, replace(firstname,"oh") FROM users;

 +-----------+----------+
 | firstname | Column_2 |
 +-----------+----------+
 | John      | Jn       |
 | Peter     | Peter    |
 | Mary      | Mary     |
 +-----------+----------+