I am currently trying to edit my db named boh. The current table "files" has a field called "path". Inside the path field is an actualpath to files listed in a folder, syntax "F:\xxx\xxx\xxx\filename.xxx". How do I update the field information to replace the "F:\xxx\xxx\xxx" so that just the file name exists?
A:
UPDATE files
SET path = REPLACE(path, 'F:\xxx\xxx\xxx\', '')
WHERE path LIKE = 'F:\xxx\xxx\xxx\%'
It's very easy to ruin your data with this massive updates so make sure you:
- Try it first with a SELECT sentence
- Backup your data
Álvaro G. Vicario
2009-12-23 12:25:33
+2
A:
It depends what you exactly want, if you want to strip constant path you can use:
UPDATE `table` SET `path` = REPLACE(`path`, 'F:\\xxx\\xxx\\xxx', '');
If you want to keep only last part after last \, then following command should do it:
UPDATE `table` SET `path` = SUBSTRING_INDEX(`path`. '\\', -1);
Michal Čihař
2009-12-23 12:26:58
thanks for the advice, I was attempting it improperly as I didn't use the "\\" when removing the directory path. Props to you!
Nandeo
2009-12-26 09:41:54
A:
Assuming 'F:\xxx\xxx\xxx\' is not constant you could try a statement like this one:
UPDATE files SET path = REVERSE(SUBSTR(REVERSE(path), 1, LOCATE(REVERSE(path), '\')));
pako
2009-12-23 12:29:09