How to replace all instances of a particular value in a mysql database with another?

Question

I'm looking for a MySQL equivalent of what str_replace is for PHP. I want to replace all instances of one word with another, I want to run a query that will replace all "apples" with "oranges".

The reason why:

UPDATE fruits SET name='oranges' WHERE name='apples';

isn't going to work for my situation, is because I often times have multiple words in a table row separated by commas like: "apples, pears, pineapples". In this case I want just apples to be replaced by oranges and pear and pineapples to stay in tact.

Is there any way to do this?

Answer 1

I think you want to use REPLACE():

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

Answer 2

Not reliably. There is REPLACE(), but that will only work until you decide to add pineapples to your menu.

Putting your database in First Normal Form and then using UPDATE is the only reliable solution.

Answer 3

Below will replace all occurances of 'apples' with 'oranges' in the 'Name' column for all the rows in the 'Fruits' table.

UPDATE fruits SET Name=REPLACE(Name,'apples','oranges')

Answer 4

You have a database design problem, as Ignacio has pointed out. Instead of including separate pieces of information included in a single column, that column should become a separate table with one piece of information per row. For instance, if that "fruits" field is in a table called "hats", you would have one table for "hats" with a column "hat_id" but no information about fruits and a second column "hat_fruits" with two columns, "hat_id" and "fruit_name". In your example, the given hat would have three rows in "hat_fruits", one for each fruit.

Once you implement this design (if you have control of the database design) you can go back to use the simple UPDATE command you originally had. In addition, you will be able to index by fruit type, search more easily, use less disk space, validate fruit names, and not have any arbitrary limit on the number of fruits that fit into the database

That said, if you absolutely cannot fix the database structure, you might try something like this:

 REPLACE(REPLACE(CONCAT(',', fruits, ','), ', ', ','), ',apples,', ',oranges,')

This monstrosity first converts the fruits field to begin and end with commas, then removes any spaces before commas. This should give you a string in which fruit names are unambiguously delimited by commas. Finally, it replaces the ,apples, (note the delimiters) with ,oranges,.

After that, of course, you ought to strip off the beginning and ending commas and put back the spaces after the commas (that's left as an exercise for the reader).

Update: Okay, I couldn't resist looking it up:

 REPLACE(TRIM(',' FROM REPLACE(REPLACE(CONCAT(',', fruits, ','), ', ', ','), ',apples,', ',oranges,')), ',', ' ,')

Note that this isn't tested and I'm not a MySQL expert anyway — I don't know if MySQL has function nesting issues or anything like that.

PS: Don't tell anyone I was the one who showed you this!