string_expression2 is a string to be searched from string_expression1. If you specify characters that are not in string_expression1 then it returns string without replacing i.e. specified string in string_expression1.
string_expression3 is a new value provided to replace string_expression2 from string_expression1.
It returns either character data type or binary data type.
Length of string_expression2 and string_expression3 can vary i.e. not necessary to have same length.
Replace function replaces all occurrence of string_expression2 from string_expression1 by string_expression3.
Examples of REPLACE Function :
Example 1 : Use of REPLACE function in select clause
SELECT REPLACE('Syntax-Example', 'Example','Explanation')
Output
Syntax-Explanation
Above example returns new string by replacing 'Example' by 'Explanation' from specified string.
Example 2 : Use of REPLACE function to display field value of table in a select clause
SELECT ContactName, REPLACE(ContactName,'an','en') AS 'New Name'
FROM Customers
Output
ContactName New Name
Maria Anders Maria enders
Ana Trujillo ena Trujillo
Antonio Moreno entonio Moreno
Thomas Hardy Thomas Hardy
Christina Berglund Christina Berglund
Hanna Moos Henna Moos
Above example replaces customers name containing 'an' by 'en' from customers table.