MySQL REPLACE() – Edit Strings and Replace Values in MySQL

By /
REPLACE Function

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

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

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

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

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

All occurrences of ‘dog’ are replaced with ‘cat’.

MySQL REPLACE() WITH Table Data

Consider the below Employee table.

Employee Table Reverse
Employee Table

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

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