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-51 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

Output:

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

Example 12-52 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

Output:

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

Example 12-53 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

Output:

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