REPLACE function replaces a string with the specified value. The comparison is case-sensitive.
Quick Example:
-- Replace word 'York' with 'Haven' in string 'New York' ('New York' -> 'New Haven') SELECT REPLACE('New York', 'York', 'Haven');
Overview:
Syntax | REPLACE(original_string, what_replace, replace_to) |
Case Sensitive | ![]() |
Use to Remove String | ![]() |
Version: MySQL 5.6
You can use REPLACE function to replace one substring or character with another. Note that the comparison is case-sensitive:
SELECT REPLACE('New York', 'York', 'Haven'); -- Result: New Haven -- Nothing is replaced as 'york' not found SELECT REPLACE('New York', 'york', 'Haven'); -- Result: New York -- Replace a character SELECT REPLACE('New', 'N', 'F'); -- Result: Few
Remove String or Character:
You can use REPLACE to remove a substring or character (replace with ''empty string):
SELECT REPLACE('New York', 'York', ''); -- Result: New SELECT REPLACE('abc', 'b', ''); -- Result: ac
Replace Data in Table:
REPLACE function does not change the original string value, it just replaces the specified occurrences with a new value, and returns a new string.
To replace values in a column of a table you have to use UPDATE statement and assign the new value to the column:
CREATE TABLE content (c1 TEXT); -- Insert sample data first INSERT INTO content VALUES ('<b>some text</b>'); -- Replace <b> with <strong> and </b> with </strong> UPDATE content SET c1 = REPLACE(c1, '<b>', '<strong>'); UPDATE content SET c1 = REPLACE(c1, '</b>', '</strong>');
MySQL REPLACE function in other databases:
PostgreSQL:
REPLACE(original_string, what_replace, replace_to) | The same syntax |
Case Sensitive | ![]() |
Can be Used to Remove String or Characters | ![]() |