Mysql Update Field Contents

Question

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?

Answer 1

did you read this?

http://dev.mysql.com/doc/refman/5.1/en/replace.html

Answer 2

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

Answer 3

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);

Answer 4

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), '\')));