MySQL REPLACE() Function And MySQL REPLACE INTO

Learn about MySQL REPLACE() function and MySQL REPLACE INTO Statement with examples through this tutorial:

MySQL provides 2 variants of REPLACE, one as a REPLACE String Function and another as a REPLACE Statement, which is like using an INSERT Statement.

As the name suggests, the REPLACE function is used to replace the matching expression or string with the replacement String or value provided as an argument to the function.

=> Click here for the complete MySQL tutorial series

MYSQL REPLACE

The REPLACE function comes under the category of String functions in MySQL.

There is another variant available in MySQL, which is a REPLACE INTO Statement. Unlike substituting a specific string, REPLACE statement essentially replaces the entire row in a MySQL table. We will see examples for both variants.

Test Data:

Let’s create a sample table and add some sample data to understand the concepts better.

CREATE TABLE employee(id INT AUTO_INCREMENT PRIMARY KEY, firstname VARCHAR(100), 
lastname VARCHAR(100), city VARCHAR(100), designation VARCHAR(100));

INSERT INTO employee VALUES
(1, 'Alex', 'Smith', 'Denver', 'Software Developer'), 
(2, 'Adam', 'Johnson', 'Vancouver', 'Marketing Manager'), 
(3, 'Peter', 'Fennel', 'Chicago', 'Sales Manager'),
(4, 'Ajay', 'Sharma', 'Mumbai', 'Software Developer'),
(5, 'Neha', 'Gupta', 'Delhi', 'Human Resources')

Syntax:

REPLACE(string-input, matching-string, replacement-string)

Let’s try to understand different components here:

• String-input: This is the Input that we are supplying to the REPLACE function. This can be an actual String value of a column containing a String value.
• Matching-string: This denotes the character or String that needs to be matched against the String input.
• Replacement-string: This is the String that would be replaced against each matching sequence.

Note: It’s important to understand the REPLACE function does a CASE-SENSITIVE match.

REPLACE Examples In MySQL

#1) Replacing a character in a given String

Here we are replacing occurrences of letter ‘H’ with ‘HH’ and aliasing the new column with replaced text as replaced_string.

SELECT REPLACE('HELLO', 'H', 'HH') as replaced_string;

Output:

#2) Replacing a String against given Input

Here we are replacing the string ‘abt’ with ‘about’.

SELECT REPLACE('abt the product', 'abt', 'about') as replaced_string;

Output: 

#3) Updating Table with MySQL REPLACE Statement

Let’s now look at an example where we want to update a table column using the REPLACE function.

Consider the sample data for the employee table (refer to the Test-Data section of the article). Suppose we want to update the designation and change the word `Manager` to `Mgr` for all the rows.

We can easily achieve this using the REPLACE statement.

UPDATE employee
SET designation = REPLACE(designation,'Manager','Mgr')

Execute the SELECT statement to view the output:

SELECT * FROM employee; 

Output:

We can see in the above output table that all designations having the word ‘Manager’ has been changed to ‘Mgr’.

UPDATE statements with REPLACE are very useful where bulk updates can be done against a column value without replacing the entire column content

For example: Suppose there’s a table containing product description, and it has a lot of spelling mistakes, for example, about spelled as about, etc. Then, by using REPLACE, this could be easily achieved over the entire table.

MySQL REPLACE Statement

The REPLACE command works similar to INSERT, the only difference being for the matching row, the data for the existing row is replaced and if there’s no existing row (as compared against primary/unique keys) a new row is inserted.

Syntax:

REPLACE INTO {table_name} 
[colName1, colName2 ...] 
VALUES (value_list)

Let’s understand the syntax:

• Here {table_name} represents the table where we want to REPLACE an existing row (or add a new row).
• Optional column list for the data to be inserted. This option is generally used when you are specifying values only for the NON NULL fields or changing the order of the columns.
• Value_list represents values for individual columns in the table.

REPLACE Statement Modes

It can work in 2 modes:

1. Delete and Insert: When a matching row is found, the matched row is first deleted and the new data is then inserted in the place of the matched row.
2. Insert: This happens when there is no matching row found and a new row gets inserted into the table.

Let’s try to understand both the above modes through an example:

#1) Delete And Insert Mode

Regarding the above sample data that we have inserted, suppose we want to change the values for employee#1 as below:

• Designation to Software Architect
• City to Ohio

Execute a SELECT statement before running the REPLACE statement as below:

SELECT * FROM employee where id=1;

Output:

Now, let us execute the REPLACE statement:

REPLACE INTO employee VALUES (1, 'Alex', 'Smith', 'Ohio', 'Software Architect');

You will notice that the REPLACE statement output returns – 2 row(s) affected

Run the SELECT statement for employee_id-1.

SELECT * FROM employee where id=1;

Output:

#2) Insert Mode

In this case, the row being replaced is not matched against the existing rows and hence Inserted as a new record.

Let’s try REPLACING a row with employee id – 6.
Now, since there’s no existing row with that ID (and id being PRIMARY KEY), the command execution results in a new row being inserted.

REPLACE INTO employee values (6, 'Martin', 'Klark', 'New York', 'Software Tester');

The output of the REPLACE command would return – 1 row(s) affected, indicating it just did an INSERT.

Run a SELECT command to query data in the table.

SELECT * FROM employee;

Output:

Important Tips/Notes

• The REPLACE statement makes sense to be used only with tables that have a notion of the PRIMARY or UNIQUE key since that’s the only mechanism it uses to find out if there’s an existing row with the same primary key.
  If there’s no Unique key defined for a table, all REPLACE statements would end up inserting new records in the table.
• For using MySQL REPLACE, you should have both INSERT and DELETE privileges on the table where the command is being executed
• REPLACE statement output returns no of the rows affected. If it’s 2, then it means it deleted the original row and replaced the new contents. If it’s just 1, then it would mean that it was an INSERT, as there was no matching record found.

Frequently Asked Questions

Q #1) What’s the difference between REPLACE function and REPLACE statement in MySQL?

Answer: MySQL provides 2 variants of MySQL REPLACE

• REPLACE statement is used to REPLACE an existing row in a table (based on PRIMARY KEY defined for the table).
• REPLACE() String function, which replaces a given character or word in the given String-based column value.

Q #2) How do I replace a row in MySQL?

Answer: REPLACE INTO command can be used to replace an entire row in MySQL. The row is matched against the table’s PRIMARY KEY, and if the key match is successful, then the row is replaced.

Example:

REPLACE INTO employee VALUES (1, 'Alex', 'Smith', 'Ohio', 'Software Architect');

The above command would replace the value with id-1 in the employee table if the ID is the primary key and there already exists a row that has ID-1

Q #3) How do you replace a word in MySQL?

Answer: Using the MySQL REPLACE() function, we can either replace individual characters as well as entire words.
Let’s look at some examples below:

Replacing character:

SELECT REPLACE('Test', 'e', 'EE') as replaced_string

Output:

Replacing word:

SELECT REPLACE('Hello World!!','World','Friend' ) as replaced_string

Output:

Conclusion

In this tutorial, we learned about the usage of the MySQL REPLACE() function and MySQL REPLACE INTO Statement. Both the variants are very useful to perform bulk updates or for targeted string replacements within a given column or field.

REPLACE() function is a String function and can be used to replace words or characters in a given column, whereas REPLACE statement is similar to INSERT statements and can be used to INSERT or REPLACE entire rows in the MySQL table.