MySQL REPLACE – Replacing text in MySQL

Overview

MySQL replace is a mysql function that allows you to replace one bit of text with another piece of text during a MySQL query. The best way to understand how a MySQL REPLACE Function works is to see it work as you will see in the example below.
The MySQL replace function works similar to how the PHP str_replace function works however the parameters are in different order.

MySQL REPLACE

The function is called mysql replace() and takes 3 parameters which are:

  1. the mysql column name
  2. the text you want to be replace
  3. the text to replace the existing text with

For example, the function will look something like this:

REPLACE(column_name, 'replace_this', 'with_this_text');

Example 1 – SELECT and REPLACE

In this example, any results that have John as their first name will instead return Johnathon as their firstname and any other names will not be affected.

SELECT REPLACE(`firstname`,'John','Johnathon') AS firstname,lastname,age FROM userdatabase;

 

Example 2 – UPDATE and REPLACE

This will update and switch one bit of text with another. Here we are going to change some html and replace <i> tags with <em> tags.

UPDATE wp_posts SET post_content = REPLACE(post_content,'<i','<em');

Now all the italic html tags would have ben replace with emphasized tags.