Page 1 of 1

Query Question.

Posted: Wed Mar 08, 2006 6:00 pm
by $var
What would the SQL statement be to delete part of a string from an entry.
i want to change imi/picture.jpg to picture.jpg in over 250 entries.

I tried winging it with this:
DELETE 'imi/' IF EXISTS 'Pictures'

but obviously it is incorrect.

can anyone help?

Posted: Wed Mar 08, 2006 6:15 pm
by Chris Corbyn
DELETE is to delete a row. You need an UPDATE.

If it's always the same string you're removing then try:

Code: Select all

update tablename set fieldname = substr(fieldname,4) where substr(fieldname,1,4) = 'imi/';
EDIT | Make sure you have a backup first!

Posted: Wed Mar 08, 2006 7:48 pm
by $var
thanks!

Posted: Fri Mar 17, 2006 11:35 am
by $var
note: when i actually did this with this command, i still ended up with /img_photo.jpg, not img_photo.jpg
in fact, when i rewrote it as:

Code: Select all

update tablename set fieldname = substr(fieldname,1) where substr(fieldname,1,1) = '/';
it still didn't remove the slash. does this command not remove slashes?