Regular Expression: Replace values according to a translation table

Question

How can I replace a list of values like

married
single
non
married
couple

to a list like this using a regular expression

Status 2
Status 1
non
Status 2
couple

? I know can match each group by something like this

/(married|single)/gm

and that I can address the matched group by $1, $2, ... . But how can I address and/or if-else the group-value in the replace-part to acutally translate the values?

Edit

Let's say I have the values to replace in a MariaDB-colum marital in myTable. Then I can do something like

SELECT 
marital,
REGEXP_REPLACE(REGEXP_REPLACE(marital,
  "married", "Status 2")
, "single", "Status 1")
FROM myTable

To get the desired result. But Is there a way to do this with just one REGEXP_REPLACE?

Thanks for your help!

Answer 1

You cannot do it with a single REGEXP_REPLACE because MariaDB doesn't support the required features in the third parameter.

You may do it using PHP with arrays: http://php.net/manual/en/function.preg-replace.php or with callback: http://php.net/manual/en/function.preg-replace-callback.php

You may do it using Perl: How to replace a set of search/replace pairs?