MySQL Replace


Another quick post on a little MySQL function I learned about recently.  As with many such things, its one of those things I discovered only when I needed it (it’s not like I go reading the MySQL manual for fun or anything LOL).

So the issue in this case was after spending 45 minutes or so running scripts to do some data imports and conversions, I realized I misspelled department as “deparment.” Now, I could just fix the PHP code (which I did) and then rerun the scripts, but then that’s another 45 minutes or so for my partner while I go through the whole sequence again.

Fortunately, I vaguely remembered having had to do this before, so quick Google search to refresh my memory and yep, MySQL has a replace function.  It does much like you expect, replacing all occurrences of the needle with the indicated string.  It’s a simple, case-sensitive search, but for my case, it worked quite well.

UPDATE my_table

SET naughty_field = REPLACE(service_configuration, 'deparment', 'department')

Less than a second later, table all corrected, versus having to rerun the whole thing.  Yay!

For a more complex use case, we can turn to a query that searches against a table of contacts by phone number…and that needs to be able to handle the phone number saved in the DB (saved formatted) AND whatever format the user throws in the search.  Using stacked replaces, we strip the formatted number back to numeric form and in the code PHP’s preg_replace does the same for the user’s search term.  This is a simplified example:

SELECT contact_id, first_name, last_name, phone_number, email_address

FROM contacts

WHERE replace(replace(replace(replace(replace(phone_number, ')', ''), '(', ''), '-', ''), ' ', ''), '.', '') LIKE '%" . preg_replace('/[^0-9]+/', '', $search_term) . "%'")

This allows for partial matches as well and lets us have a reverse lookup system on our directory. 🙂