How can I find and replace in MySQL in a column with file path?

For thus, use MySQL REPLACE(). Let us first create a table −

mysql> create table DemoTable
(
   FolderLocation text
);
Query OK, 0 rows affected (0.80 sec)

Insert some records in the table using insert command −

mysql> insert into DemoTable values('C/ProgramFiles/AllMySQLProgram');
Query OK, 1 row affected (0.13 sec)
mysql> insert into DemoTable values('C/ProgramFiles/JavaChatApplication');
Query OK, 1 row affected (0.14 sec)
mysql> insert into DemoTable values('C/ProgramFiles/Main/Image.png');
Query OK, 1 row affected (0.18 sec)

Display all records from the table using select statement −

mysql> select *from DemoTable;

This will produce the following output −

+------------------------------------+
| FolderLocation                     |
+------------------------------------+
| C/ProgramFiles/AllMySQLProgram     |
| C/ProgramFiles/JavaChatApplication |
| C/ProgramFiles/Main/Image.png      |
+------------------------------------+
3 rows in set (0.00 sec)

Following is the query to perform find and replace in MySQL −

mysql> update DemoTable
   set FolderLocation=replace(FolderLocation,'C/ProgramFiles','E/MyFolder/Details');
Query OK, 3 rows affected (0.14 sec)
Rows matched: 3 Changed : 3 Warnings : 0

Let us check the table records once again −

mysql> select *from DemoTable;

This will produce the following output −

+----------------------------------------+
| FolderLocation                         |
+----------------------------------------+
| E/MyFolder/Details/AllMySQLProgram     |
| E/MyFolder/Details/JavaChatApplication |
| E/MyFolder/Details/Main/Image.png      |
+----------------------------------------+
3 rows in set (0.00 sec)

AmitDiwan

e

Updated on: 10-Oct-2019

345 Views

Kickstart Your Career

Get certified by completing the course

Get Started