Find and replace by last occurrence in MySQL

Doing find and replace tasks are very common in IT. Usually we trying to find a substring and replace it with something else. Typical needle and haystack kind of stuff. But the thing is that the “find” part is based on the first occurrence of the needle in the haystack.

In this blog post, I want to show you how to do a typical find and replace in MySQL based on the last occurrence of a string.

Last occurrence find and replace

The dummy query above looks for the term “feryn.eu” in the string “feryn.eu.bla.feryn.eu” and replaces the last occurrence of “feryn.eu” with “thijs.be”. The end result is the string “feryn.eu.bla.thijs.be”.

This query doesn’t really return the position, but rather the data that comes before the last occurrence of our so-called needle.

I didn’t figure this out myself, but found it on Stackoverflow.

Use it in an update statement

Want to start using the find and replace trick in an update statement? Here’s an example:

In the example above, I’m replacing the last occurrence of “feryn.eu” by “thijs.be” in the “someField” field.