MYSQL:Looking for equivalent of Perl's "regex" =~ s/e/i/g => rigix in a MySQL Select

Question

Can I match and replace a text pattern in a MYSQL select?

---

EDIT For now it looks like the answer is: Can't be done, since you can't capture what was matched (from Eric's answer / comments). For now I will look into adding a lookup table.

---

Simplified example:

The MySQL table Coleridge holds many strings like:

text
------------------------------------
In_Xanadu_did_Kubla_Khan
A_stately_pleasure_dome_decree
Where_Alph_the_sacred_river_ran
Through_caverns_measureless_to_man
Down_to_a_sunless_sea

Is there a way to express the select

SELECT text =~ s / [ ^_ ] + _ ( .* ) _ [ ^_ ] + $ / \1 / as replaced FROM Coleridge

and get

replaced
________________________
Xanadu_did_Kubla
stately_pleasure_dome
Alph_the_sacred_river
caverns_measureless_to
to_a_s

Please Note:

1. The regular expression s/ / / I provided is much less complicated than what the real world DB contains
2. Unfortunately I can't normalize the DB ..

Answer 1

No, you can't. MySQL only supports regexes for matching (RLIKE), not replacement.

Answer 2

There's no regex way to replace anything in MySQL. You can match in MySQL based on RegEx (regexp), but that doesn't return what part is matched, just the whole column that is. But, you can use replace like so:

select replace(col, 'e', 'i') from tbl

This will return regex as rigix.

As for your specific example, you'd have to use a combination of locate and substring:

select 
    substring(col
        , locate('_', col)+1
        , locate('_', reverse(col))-(len(col)-locate('_', col)-1)) 
from tbl

The general rule on string manipulations/handling on the database level is: Keep it simple. RDBMS's think in sets, and that's where they shine. Pulling individual string manipulations is not in their sweet spot. As such, no RDBMS has really mature string handling functions (and there certainly isn't any consistency across the different ones). If your regex is reasonably complex, you will most likely want to just handle it on the presentation/app layer, and not in the database.