SQL REPLACE Function
SQL REPLACE function is used to perform a search-and-replace operation on a string. The syntax is as follows:
REPLACE (string, search, replace)
SQL REPLACE function replaces all occurrences of string2 in string1 with string3.
The following rules are used to determine the value returned if one of the string expressions contains a NULL value or is an empty, zero length, and string:
• If string1 is NULL return NULL.
• If string2 or string3 is NULL return string1.
• If string2 is empty return string1.
Example 1:
The following SQL REPLACE function returns the string “Good afternoon! Good afternoon!”
SELECT REPLACE ('Good morning! Good morning!', 'morning', 'afternoon') FROM dual;
Output:
Example 2:
The following SQL REPLACE Function replaces letter ‘E’ in names of employee with a space:
SELECT REPLACE (first_name, ‘E’, ‘ ’) FROM employee;
Output:
