MySQL - REPLACE Function - Guide, Examples and Alternatives

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

MySQL REPLACE Function Details

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 in Other Databases

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

Resources