SQL Reference - REPLACE

REPLACE(str, from_str, to_str);

str: This parameter refers to the source string where the search will be conducted.
from_str: This parameter refers to the sub-string which is to be replaced. In other words, this is the target text within the ‘str’ that will be substituted.
to_str: This is the parameter that contains the replacement text. Any instances of ‘from_str’ in the ‘str’ will be replaced with this value.

Example

SELECT REPLACE('SQL is fun', 'fun', 'fantastic');

Output

'SQL is fantastic'

Explanation

The REPLACE function is used here to replace the word “fun” with “fantastic” in the string “SQL is fun”. The new string “SQL is fantastic” is then returned.

REPLACE( string_expression , string_pattern , string_replacement )

string_expression: The string expression which represents the string that should be searched. It can be a column name, a variable, or a literal string where the specified pattern will be searched and replaced.
string_pattern: The string pattern which contributes to the portion of the string_expression that should be located and replaced. It can be either a literal string or variable.
string_replacement: This parameter represents the new string that will replace the string_pattern in the string_expression. It can be a variable or a literal string. If this parameter is empty, the function will remove all occurrences of the string_pattern from the string_expression.

Example

SELECT REPLACE('SQL is interesting', 'interesting', 'fun');

Output

SQL is fun

Explanation

In the given SQL command, REPLACE function is used to replace all occurrences of a substring within a string. The first parameter is the initial string, the second parameter is the substring to be replaced, and the third parameter is the substring to replace with. In this instance, all occurrences of ‘interesting’ in the string ‘SQL is interesting’ are replaced with ‘fun’.

REPLACE(string, search_string, replace_with_string);

string: This is the source string from which a sub-string will be replaced.
search_string: The sub-string to be located and replaced within the source string input.
replace_with_string: The new string that will take the place of the located sub-string within the source string input.

Example

SELECT REPLACE('Hello, world!', 'world', 'Oracle') FROM dual;

Output

'Hello, Oracle!'

Explanation

In this example, the REPLACE function in Oracle is used to replace all occurrences of a substring within a string. The syntax of the REPLACE function is REPLACE(string, substring_to_replace, replacement_substring). The example is replacing ‘world’ with ‘Oracle’ in the string ‘Hello, world!’, resulting in the output ‘Hello, Oracle!’. The function FROM dual is a dummy table in Oracle.

REPLACE(table, column1, new_value1, column2, new_value2, …);

table: The name of the table in which the replacements will be made.
column1, column2, …: Names of the columns in which values are to be replaced.
new_value1, new_value2, …: These are the new values that will replace the old values in the specified columns.

Example

CREATE TABLE Test (
   text varchar(255)
);

INSERT INTO Test (text)
VALUES ('Hello, World!');

SELECT REPLACE(text, 'Hello', 'Goodbye')
FROM Test;

Output

Goodbye, World!

Explanation

In the given example, a table named ‘Test’ was created and a row was inserted containing the string ‘Hello, World!’. The SQL ‘REPLACE’ function was then used to replace all occurrences of ‘Hello’ with ‘Goodbye’ in the ‘text’ column. The output is ‘Goodbye, World!’, which indicates the successful execution of the REPLACE function.