Replace one word with another in lots of rows in a database

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
oskare100
Forum Commoner
Posts: 80
Joined: Sun Oct 29, 2006 5:47 am

Replace one word with another in lots of rows in a database

Post by oskare100 »

Hello,
Is there in any way (with MySQL or PHP or software) to replace one work with another in all rows in a table?

I have about 7000 product descriptions in Swedish and I need to translate them into English. The good thing is that all the description only contain a few words (I think that I just need to replace about 30 words in total to translate everything). The bad thing is that I don't know how to do that.

So I want to select all rows with the language id for English (currently all English decriptions are in Swedish) and then replace all occurances of a specific word with another specific word.

So UDPATE description ???? WHERE language_id = 5 is what I've figured wold be a start for an MySQL command... the problem is that I don't know what to put instead of ????.

Would it be possible to do that in PHP or MySQL? Could you help me with any kind of command (is that how you say it?) or any other help?

Regards /Oskar
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Re: Replace one word with another in lots of rows in a database

Post by timvw »

At http://dev.mysql.com/doc/refman/5.0/en/ ... tions.html you should focus on replace and regexp.
AMCH
Forum Commoner
Posts: 31
Joined: Sun Mar 30, 2008 4:39 pm

Re: Replace one word with another in lots of rows in a database

Post by AMCH »

If I was doing this I would use the built in "str_replace()" function, you should research that, the link posted above will be useful for that. My pseudocode would be something like:

- loop through database rows where language should be english
- on a row by row basis take all data from the fields and write them to variables
- use string replace function to replace all variations of specific words with correct alternatives for each variable
- update the table for this specific row with the altered data
Post Reply