How to add extra folder to multiple paths stored in MySQL database? From `Folder\file.jpg` to `Folder\New Folder\file.jpg`?

Question

Details:

I have stored physical paths into MySQL table. I have moved all content to a new folder.

This is the data in the database:

Current data---

g:\Folder1\File 1.jpg
g:\Folder1\Excel File.xlsx
g:\Folder1\Test.js

Desired change:

Here is what I'd like to achieve, add an extra folder to the path before the filename.

Desired---

g:\Folder1\New Folder\File 1.jpg
g:\Folder1\New Folder\Excel File.xlsx
g:\Folder1\New Folder\Test.js

Question

How can I achieve this? And just for future, how can I remove a specific folder from the path?

Does this answer your question? [MySQL string replace](https://stackoverflow.com/questions/5956993/mysql-string-replace) — kmoser, Jun 16 '20 at 12:59

score 1 · Accepted Answer · answered Jun 16 '20 at 12:59

Simply use REPLACE as explained also in this SO question

UPDATE table
SET fied = REPLACE(field, 'g:\Folder1\', 'g:\Folder1\New Folder\')

It is untested so you may have to fix the \ escaping

Since REPLACE gives you the ability to replace the string with another one you can just modify the strings to "add or remove folders"

RusArtM · Answer 2 · 2020-06-16T13:00:58.587

0

Use replace function:

SELECT replace(filename, "Folder1\\", "Folder1\\New Folder\\")
FROM your_table;

You can specify a path from drive letter to avoid random data being replaced.

edited Jun 16 '20 at 13:00

answered Jun 16 '20 at 12:57

RusArtM

this will just display the updated data. It will not update them in the database – Lelio Faieta Jun 16 '20 at 12:59
Sure, but it gives a way to do anything with this new path - to store it or to just displa. – RusArtM Jun 16 '20 at 13:00

2 Answers2