REPLACE Function

REPLACE is like using find-and-replace in a document, swapping every copy of one piece of text for another.

In this page:

What is the REPLACE Function?
Case Sensitivity in REPLACE
Removing Substrings
Updating Data using REPLACE
Nested REPLACE Functions

Syntax

sql

REPLACE(string, search_string, replacement_string)

What is the REPLACE Function?

REPLACE scans a string for every occurrence of a target substring and swaps each one for a new value, similar to a find-and-replace in a text editor but running directly inside a query.

Example: What is the REPLACE Function?

sql

SELECT REPLACE('I like cats', 'cats', 'dogs') AS updated_text;

Case Sensitivity in REPLACE

REPLACE compares characters exactly, so it is case-sensitive by default — searching for Cat will not match cat in the source string. Keep this in mind when cleaning data that has inconsistent capitalization.

Example: Case Sensitivity in REPLACE

sql

SELECT REPLACE('I like Cats', 'cats', 'dogs') AS unchanged;
SELECT REPLACE('I like Cats', 'Cats', 'dogs') AS changed;

Removing Substrings

Passing an empty string as the replacement effectively deletes every match instead of swapping it for something else. This is a quick way to strip out unwanted symbols like dashes or parentheses from a phone number column.

Example: Removing Substrings

sql

SELECT REPLACE('(555) 123-4567', '-', '') AS no_dashes;

Updating Data using REPLACE

Pairing REPLACE with an UPDATE statement lets you fix a value across every row in a column in one pass, which is much faster than editing each record individually — useful for correcting a mistyped brand name site-wide.

Example: Updating Data using REPLACE

sql

CREATE TABLE products (id INT, brand TEXT);
INSERT INTO products VALUES (1, 'Acem'), (2, 'Acem Pro');
UPDATE products SET brand = REPLACE(brand, 'Acem', 'Acme');
SELECT * FROM products;

Nested REPLACE Functions

You can nest REPLACE calls inside one another to apply several substitutions in a single expression, such as stripping both dashes and spaces out of a phone number before storing it in a normalized format.

Example: Nested REPLACE Functions

sql

SELECT REPLACE(REPLACE('555-123 4567', '-', ''), ' ', '') AS cleaned_number;

Chapter Quiz — Complete all 7 topics to unlock

0/7 topics done

Complete these topics first:

← Previous Lesson TRIM LTRIM RTRIM Next Lesson → FORMAT Function