Can we use str_replace in MySQL?

MySQL Database Training for Beginners

39 Lectures 5.5 hours

Simon Sez IT

More Detail

Python programming with MySQL database: from Scratch

152 Lectures 16 hours

Metla Sudha Sekhar

More Detail

Learn MySQL from scratch for Data Science and Analytics

87 Lectures 5.5 hours

Metla Sudha Sekhar

More Detail

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

Updated on 30-Jul-2019 22:30:24

• Related Questions & Answers
• How can I use MySQL replace() to replace strings in multiple records?
• How can we use MySQL REPLACE statement to prevent of insertion of duplicate data?
• 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 semicolon as a MySQL DEMILITER?
• Can we use {} while creating a MySQL table?
• 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?
• Can we replace a number with a String in a MySQL result set?
• 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?