Update the LENGTH part of a string containing the KEYWORD sentence in MySQL

Question

I can't find the correct MySQL syntax for what i want to do, i even wonder if this is possible. Let me explain :

I've got a "folder" table that contains a "path" column. There are 3 folders registered :

1. \car insurance
2. \car insurance\car insurance
3. \car insurance\car insurance\motorcycle insurance

As you can notice, the first 2 folders have the same name. The second folder is contained in the first one, and the third one in the second one.

Let's say i move the first folder into a folder called "Mr Jones". So i want to update the paths to get the following result :

1. \Mr Jones\car insurance
2. \Mr Jones\car insurance\car insurance
3. \Mr Jones\car insurance\car insurance\motorcycle insurance

My MySQL query is the following one :

UPDATE folder SET path = REPLACE (path, '\\car insurance', '\\Mr Jones\\car insurance') WHERE path LIKE '\\\\car insurance%';

The problem with that query is that at the end i get the following paths :

1. \Mr Jones\car insurance
2. \Mr Jones\car insurance\Mr Jones\car insurance
3. \Mr Jones\car insurance\Mr Jones\car insurance\motorcycle insurance

Indeed, the query replaced all the occurences of "\car insurance" by "\Mr Jones\car insurance". So the first path is correct but the others are wrong.

So i tried to limit the replacement to the 15 first characters of each path.

UPDATE folder SET path = REPLACE (SUBSTRING(path, 1, 14), '\\car insurance', '\\Mr Jones\\car insurance') WHERE path LIKE '\\\\car insurance%';

The problem with that query is that it totally overwrites the rest of the string. Here is the result :

1. \Mr Jones\car insurance
2. \Mr Jones\car insurance
3. \Mr Jones\car insurance

My question is then the following one :

Is it possible possible to update only the "LENGTH" part of a string containing the "KEYWORD" sentence without erasing the whole string ?

Thanks in advance for your answers and sorry for my english, i'm french...

Answer 1

Ok, i found a solution so i will answer my own question, maybe that will help someone someday...

I changed what i wanted to change in a LENGTH subtring, then i appended the end of the original string to it with a concat... The result looks like this :

UPDATE folder SET path = CONCAT(REPLACE((SELECT SUBSTRING(path, 1, 14) AS StringtoReplace), '\\car insurance', '\\Mr Jones\\car insurance'), (SELECT SUBSTRING(path, 15) AS StringtoAdd)) WHERE path LIKE '\\\\car insurance%';

A bit long but it works...