SQL REPLACE Function: Search and Replace String in Database

Summary: in this tutorial, you will learn how to use the SQL REPLACE function to search and replace all occurrences of a substring with another substring in a given string.

Introduction to the SQL REPLACE function

Sometimes, you want to search and replace a substring with a new one in a column e.g., change a dead link to a new one, rename an obsolete product to the new name, etc.

SQL provides a very helpful string function called REPLACE that allows you to replace all occurrences of a substring in a string with a new substring.

The following illustrates the syntax of the REPLACE function:

REPLACE(string, old_substring, new_substring);Code language: SQL (Structured Query Language) (sql)

The REPLACE function will search for all occurrences of the old_substring and replace it with the new_string.

The following statement replaces all the occurrences of bar with foo so the result is bar bar bar.

SELECT REPLACE('foo foo bar', 'foo', 'bar'); -- bar bar barCode language: SQL (Structured Query Language) (sql)

Note that the REPLACE function searches for the substring in the case sensitive manner. For example, the following statement replaces foo with bar, the FOO will not be replaced because it does not match the searched string foo.

SELECT REPLACE('foo FOO bar', 'foo', 'bar'); -- bar FOO bar
Code language: SQL (Structured Query Language) (sql)

If the function cannot find the substring, it does nothing. For example, the following statement returns the original string because it cannot find any occurrences of the substring BAR.

SELECT REPLACE('foo foo bar', 'BAR', 'bar'); -- foo foo barCode language: SQL (Structured Query Language) (sql)

SQL REPLACE with the UPDATE statement

Let’s take a look at the employees table in the sample database.

The following statement returns the employee names and their phone numbers.

SELECT 
    first_name, 
    last_name, 
    phone_number
FROM
    employees
ORDER BY first_name, last_name;Code language: SQL (Structured Query Language) (sql)

Suppose you want to use the dash  ( -) character instead of dot ( .) character to format the phone numbers. In this case, you use the UPDATE statement to replace the dash character by the dot character in the phone_number column as the following statement:

UPDATE employees 
SET 
    phone_number = REPLACE(phone_number, '.', '-');Code language: SQL (Structured Query Language) (sql)

Notice that the above UPDATE statement updates all rows in the employees table.

If you update data in the production system, you should first use a SELECT statement to find the number of rows affected before doing the mass update.

For example, the following statement updates the email of employees from sqltutorial.org to acme.com for the employee id 100:

UPDATE employees 
SET 
    email = REPLACE(email,
        'sqltutorial.org',
        'acme.com')
WHERE
    employee_id = 100;Code language: SQL (Structured Query Language) (sql)

Let’s check the result.

SELECT 
    employee_id, 
    first_name, 
    last_name, 
    email
FROM
    employees
WHERE
    employee_id = 100;    
Code language: SQL (Structured Query Language) (sql)

It works as expected. So we can apply the changes to all rows by removing the WHERE clause.

UPDATE employees 
SET 
    email = REPLACE(email,
        'sqltutorial.org',
        'acme.com');Code language: SQL (Structured Query Language) (sql)

Notice that it is easy to make a mistake to use the column name as a literal string for the first argument of the REPLACE function as follows.

UPDATE employees 
SET 
    email = REPLACE('email',
        'sqltutorial.org',
        'acme.com')Code language: SQL (Structured Query Language) (sql)

Your intention is to replace the sqltutorial.org in the email column with acme.com. However, this statement will update all values in the email columns to email because the result of the following expression is a literal string email.

REPLACE('email','sqltutorial.org','acme.com'); -- emailCode language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the SQL REPLACE function to search and replace all occurrences of a substring with a new string.