In this tutorial, we will study the MySQL REPLACE()
function. Sometimes, you may have to change the value of a substring within a string. This is where the MySQL REPLACE()
function comes into the picture. The REPLACE()
function is used to replace all the occurrences of a substring, with a new string. The REPLACE()
function performs a case-sensitive replacement.
Syntax for MySQL REPLACE()
REPLACE(string, old_substring, new_substring)
Code language: SQL (Structured Query Language) (sql)
Where ‘old_substring’ is the value that should be replaced by ‘new_substring’.
Examples of MySQL REPLACE()
Let’s go over some of the basic examples of the MySQL REPLACE() function.
Basic Examples
Let us look at a few basic examples. We will use an alias called NewString
to make our result readable.
Replace the word ‘dog’ in ‘I saw a dog’ with ‘cat’.
SELECT REPLACE("I saw a dog", "dog", "cat") AS NewString;
Code language: SQL (Structured Query Language) (sql)
Output –
![Replace Basic Example1](https://mysqlcode.com/wp-content/uploads/2020/12/replace-basic-example1.png)
NULL Arguments – Replace the word ‘dog’ in ‘I saw a dog’ with NULL and the word NULL in ‘I saw a dog’ with ‘cat’.
SELECT REPLACE("I saw a dog", "dog", NULL) AS NewString;
SELECT REPLACE("I saw a dog", NULL, "cat") AS NewString;
Code language: SQL (Structured Query Language) (sql)
Output –
![Replace Basic Example2](https://mysqlcode.com/wp-content/uploads/2020/12/replace-basic-example2.png)
Replacing a substring within a string with NULL makes the entire string NULL.
Embedded Strings – Replace the word ‘do’ in ‘I saw a dog’ with ‘lo’.
SELECT REPLACE("I saw a dog", "do", "lo") AS NewString;
Code language: SQL (Structured Query Language) (sql)
Output –
![Replace Basic Example3](https://mysqlcode.com/wp-content/uploads/2020/12/replace-basic-example3.png)
Case Sensitivity and No Match – Replace the word ‘DOG’ in ‘I saw a dog’ with ‘cat’.
SELECT REPLACE("I saw a dog", "DOG", "cat") AS NewString;
Code language: SQL (Structured Query Language) (sql)
Output –
![MySQL REPLACE Basic Example4](https://mysqlcode.com/wp-content/uploads/2020/12/replace-basic-example4.png)
REPLACE()
is case-sensitive. Since there is no substring ‘DOG’ in the string, there is no change in the original string.
Multiple Occurrences – Replace the word ‘dog’ in ‘The dog jumped over another dog’ with ‘cat’.
SELECT REPLACE("The dog jumped over another dog", "dog", "cat") AS NewString;
Code language: SQL (Structured Query Language) (sql)
Output –
![MySQL REPLACE Basic Example5](https://mysqlcode.com/wp-content/uploads/2020/12/replace-basic-example5.png)
All occurrences of ‘dog’ are replaced with ‘cat’.
MySQL REPLACE() WITH Table Data
Consider the below Employee table.
![Employee Table Reverse](https://mysqlcode.com/wp-content/uploads/2020/12/employee-table-reverse.png)
Now let us look at the following query.
SELECT Name, REPLACE(Name, 'ika', 'a') AS PetName FROM Employee WHERE eid=2;
Code language: SQL (Structured Query Language) (sql)
With the help of the SELECT statement and WHERE clause, we are replacing ‘ika’ in the name of the employee with eid=2 with ‘a’ and giving the result an alias named PetName. Our output is:
![Replace Table Example](https://mysqlcode.com/wp-content/uploads/2020/12/replace-table-example.png)
Conclusion
The REPLACE()
function is very useful in string operations and manipulations. For more information, I would highly recommend you to check out the below link.
References
- MySQL Official Documentation on
REPLACE()