MySQL Replace function

Posted by on December 2, 2011 in Database, MySQL | 0 comments

I don’t recommend over using the REPLACE() function in MySQL.  It completely destroys performance.  However, sometimes, either by poor design, or just oversight, you have to replace something in order to make a join work, or the application code work.  REPLACE() is case-sensitive. The syntax is pretty simple:

REPLACE(`field`, ‘find’, ‘replacewith’)

Example:

Fruit Values: Apple, Orange, Pear, Grape
REPLACE(`Fruit`, ‘a’, ‘EEE’);
Result: Apple, OrEEEnge, PeEEEr, GrEEEpe

You can use it in a variety of ways. You can even change `field` from a table column to a constant if you really need to, but again I wouldn’t overuse this function. When you do calculations or use functions in MySQL, depending on the function, it usually takes you from a quick query that may be able to use an index to a query that looks at every row, unless you can filter it down using other indexed columns. Not only that, instead of just comparing a value, it actually has to calculate your value before it compares, either by string manipulation or arithmetic.

If you enjoyed this post, please consider leaving a comment or subscribing to the RSS feed to have future articles delivered to your feed reader.

Leave a Comment

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>