REPLACE Function
In this page:
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?
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
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
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
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
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: