CODEWITHSUNDEEP
mysqlreplacesql-update
user237563
user237563

Reputation:

Mysql Update Field Contents

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?

Upvotes: 2

Views: 373

Answers (4)

pako
pako

Reputation: 1919

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

Upvotes: 0

Michal Čihař
Michal Čihař

Reputation: 10091

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

Upvotes: 3

Álvaro González
Álvaro González

Reputation: 146450

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

Upvotes: 0

Murat Kucukosman
Murat Kucukosman

Reputation: 632

did you read this?

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

Upvotes: 1

Related Questions