SQL Replace function in sql Select and Update query


Example

The Replace function in SQL is used to update the content of a string. The function call is REPLACE( ) for MySQL, Oracle, and SQL Server.

The syntax of the Replace function is:

REPLACE (str, find, repl)

The following example replaces occurrences of South with Southern in Employees table:

FirstNameAddress
JamesSouth New York
JohnSouth Boston
MichaelSouth San Diego

Select Statement :

If we apply the following Replace function:

SELECT 
    FirstName, 
    REPLACE (Address, 'South', 'Southern') Address
FROM Employees 
ORDER BY FirstName 

Result:

FirstNameAddress
JamesSouthern New York
JohnSouthern Boston
MichaelSouthern San Diego

Update Statement :

We can use a replace function to make permanent changes in our table through following approach.

Update Employees 
Set city = (Address, 'South', 'Southern');

A more common approach is to use this in conjunction with a WHERE clause like this:

Update Employees 
Set Address = (Address, 'South', 'Southern')
Where Address LIKE 'South%';