MySql Replace Function

The previous lesson of MySql Lessons Series was about MySql Case Statement and in this lesson we will learn about MySql Replace Function.



MySql Replace function is used whenever we want to replace a character or a group of characters with another set of characters or character. The basic structure of MySql Replace function is given below:

UPDATE TableName SET ColumnName = 
REPLACE(ColumnName,Characters To Replace,Replace With)

For explanation of MySql Replace function consider the table (Names) shown in the image below:



The (Names) table shown in the above image shows the first name and last name of users seperated by (-). We can use MySql Replace function to replace the dash (-) with a space ( ) like this:

UPDATE Names SET Name = REPLACE(Name, '-', ' ') 

The above query will replace (-) between all the names of the column (Name) with a space. The output of the above query is shown in the image below:



If we want to replace the dash (-) for only a few records or a single record of the column (Name), we can use WHERE clause in the query to prevent the application of replace function to all records of the (Name) column i.e

UPDATE Names SET Name = REPLACE(Name, '-', ' ') WHERE id = 3

The replace function in the above query will run for only a single record with id = 3.

Related Posts
MySql CONCAT() Function
MySql FIND_IN_SET and MySql Locate
CASE in MySQl
MySql IF Statement