Back to cookbooks list Articles Cookbook

How to Replace Part of a String in SQL



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


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

1Big Market123–300-400
3The Sunny Restaurant123–222-456
4My Bank123-345-400

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


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

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

123 300 400
123 222 456
123 345 400


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.

Small Market

Recommended courses:

Recommended articles:

See also: