views:

34

answers:

4

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?

+1  A: 

did you read this?

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

Murat Kucukosman
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
+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ř
thanks for the advice, I was attempting it improperly as I didn't use the "\\" when removing the directory path. Props to you!
Nandeo
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