Can we use str_replace in MySQL?

The str_replace version in MySQL is the replace() function. Let us first create a table to understand the function −

mysql> create table StringReplaceDemo
   −> (
   −> Id int,
   −> URL varchar(200)
   −> );
Query OK, 0 rows affected (0.38 sec)

Insert some records in the table with the help of insert command. The query is as follows −

mysql> insert into StringReplaceDemo values(1001,'https://www.google.co.in');
Query OK, 1 row affected (0.09 sec)

mysql> insert into StringReplaceDemo values(1002,'https://www.facebook.com');
Query OK, 1 row affected (0.11 sec)

mysql> insert into StringReplaceDemo values(1003,'https://mail.google.com');
Query OK, 1 row affected (0.12 sec)

Display all records from the table using the following query −

mysql> select *from StringReplaceDemo;

The following is the output −

+------+--------------------------+
| Id   | URL                      |
+------+--------------------------+
| 1001 | https://www.google.co.in |
| 1002 | https://www.facebook.com |
| 1003 | https://mail.google.com  |
+------+--------------------------+
3 rows in set (0.00 sec)

Look at the above sample output. We will now replace the word ‘mail’ with ‘www’. The query is as follows −

mysql> update StringReplaceDemo set URL = replace(URL,'mail','www') where URL like '%mail%';
Query OK, 1 row affected (0.15 sec)
Rows matched: 1 Changed: 1 Warnings: 0

Now let us check the word mail is replaced or not. The query is as follows −

mysql> select *from StringReplaceDemo;

The following is the output displaying that we have successfully performed replace −

+------+--------------------------+
| Id   | URL                      |
+------+--------------------------+
| 1001 | https://www.google.co.in |
| 1002 | https://www.facebook.com |
| 1003 | https://www.google.com   |
+------+--------------------------+
3 rows in set (0.00 sec)

Rishi Rathor

Published on 11-Jan-2019 09:51:02

• Related Questions & Answers
• How can we use MySQL SUM() function?
• How can we use prepared statements in MySQL?
• How can we use nested transactions in MySQL?
• Can we use ORDER BY NULL in MySQL?
• Can we use {} while creating a MySQL table?
• Can we use semicolon as a MySQL DEMILITER?
• str_replace() function in PHP
• How can we use nested transactions allowed in MySQL?
• Can we use LIKE and OR together in MySql?
• Can we use backticks with column value in MySQL?
• How can we use MySQL SELECT without FROM clause?
• How can we create and use a MySQL trigger?
• How can we use MySQL UNION operator on datasets?
• How can we use SIGNAL statement with MySQL triggers?
• Can we use IFNULL along with MySQL ORDER BY?