[Resolved] SQL Update/Replace Help

[The Hobo]

Let's say I want to run a query that will replace the word dog with cat in all records. How could I accomplish that?

I know worst case scenario, I could just loop through each record, run str_replace() on it, then update the record with the new text, but I'm hoping there's an easier way?

[morrowasted]

unfortunately, no, there isnt an easier way (at least i can't think of any.)

sorry.

you'll just have to loop the str_replace().

of course, they might be able to answer your question here

(no im not advertising, i just know some very smart PHPers over there)

[The Hobo]

Bummer. I'll just have to do it that way for now, I guess.

[morrowasted]

Let me run it by some mates and see what they think, and I'll come back to you with an answer (if there is one)

[morrowasted]

he says 'try this':

UPDATE tablename SET columnname = 'whatever' 

[The Hobo]

That would replace every columnname in the record with 'whatever' ...

[phpman]

columnname = fieldname

yes that would work hobo. isn't that what you wanted?

[The Hobo]

Originally posted by phpman
columnname = fieldname

yes that would work hobo. isn't that what you wanted?

No.

Let's say I have a field called 'message' and I want to replace every occurance of the word dog with the word cat.

So this:

My dog is a cool dog not like other dogs in the dog town.

Would become:

My cat is a cool cat not like other cats in the cat town.

[phpman]

if this

"My dog is a cool dog not like other dogs in the dog town."

is in the field message then you have to query the db twice to get it and replace it. that code morrow gave you will only do one word or a number per field.

so you have to query the db like so

select message from table where message like '%dog%'

then replace all of the them with cat and update the db again with the new line.

sorry I didn't realize that it was a sentence you wanted.

[The Hobo]

So...I have to do what I refered to as the 'worst case scenario' above? Peachy

[phpman]

yeah sorry, I can't think of anything that will do what you want at the moment.

I was thinking of REPLACE but after looking I don't think that will work either. if it was just a single word in the field it would be easy, but seeing how it is a sentence I don't see any other way.

[The Hobo]

Seems like that functionality would exist, but it's no big deal.