How to Replace Part of a String in SQL

Problem:

You’d like to replace part of a string with another string.

Example:

Our database has a table named investor with data in the following columns: id, company, and phone.

We’d like to change the phone number format for each company by replacing the hyphen character with a space.

Solution:

SELECT REPLACE(phone, '-', ' ') as new_phone
FROM investor;

The query returns a list of phone numbers with spaces instead of hyphens as separators:

Discussion:

If you’d like to replace a substring with another string, simply use the REPLACE function. This function takes three arguments:

• The string to change (which in our case was a column).
• The substring to replace.
• The string with which to replace the specified substring.

In the next example, we replace the adjective 'Big' in the company column with 'Small'.

SELECT REPLACE( company, 'Big', 'Small' ) as new_company
FROM investor
WHERE id = 1;

This query display the new name of the company for the investor with id = 1.