Articles Cookbook
Back to list
Standard SQL

How to Replace Part of a String in SQL

Database:

Operators:

REPLACE

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.

idcompanyphone
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.

Solution:

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

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

new_phone
123 300 400
123 222 456
123 345 400

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.

new_company
Small Market

Recommended courses:

Recommended articles:

See also:

go to top