MySQL REPLACE

REPLACE() function in MySQL replaces all occurrences of a substring in a string.
 

Example 1:

In the example below, we replace “Java” with “MySQL” in “Learn Java” string and the column returned will contain “Learn MySQL”.

SELECT REPLACE("Learn Java", "Java", "MySQL");

 

 

Syntax:

REPLACE(str1, str2, str3)

• str1 : The original string
• str2 : The substring to replace
• str3 : The new substring

 

Example 2:

Consider the following table of images:

+------------+--------------+
|     id     |      name    |
+------------+--------------+
|          1 | image001.jpg |
|          2 | image002.jpg |
|          3 | image003.jpg |
|          4 | image004.jpg |
|          5 | image005.jpg |
+------------+--------------+

Suppose, we want to replace the substring “image” in the column “name” with an empty string. This can be done with the following query:

UPDATE Images set name = replace(name, 'image', '');

We will have the following output:

+------------+--------------+
|     id     |      name    |
+------------+--------------+
|          1 |      001.jpg |
|          2 |      002.jpg |
|          3 |      003.jpg |
|          4 |      004.jpg |
|          5 |      005.jpg |
+------------+--------------+

MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More