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

Updated on 10-Oct-2019 12:25:27

• Related Questions & Answers
• MySQL: How can I find a value with special character and replace with NULL?
• How to get left substring in MySQL from a column with file path? Display the entire file path string excluding the file name?
• How can MySQL find and replace the data with REPLACE() function to UPDATE the table?
• How can I use MySQL replace() to replace strings in multiple records?
• How can I replace & with an ampersand in my MySQL database?
• How can I replace newlines with spaces in JavaScript?
• Given a column name how can I find which tables in a MySQL database contain that column?
• Update a column of text with MySQL REPLACE()
• How can I remove every column in a table in MySQL?
• How can I use MySQL INTERVAL() function with a column of a table?
• How i can replace number with string using Python?
• How to find and replace within a text file using Python?
• How can I open a VDS file with three js?
• How to find and replace the word in a text file using PowerShell?
• How to replace a particular character in a MySQL column?