MySQL 5.7: REGEXP version of PostgreSQL REGEXP_REPLACE

Question

I have a CASE STATEMENT in PostgreSQL which I need to convert into a MySQL statement.

 CASE
 WHEN mobilenumber ~'^09[0-9]'
 THEN regexp_replace(mobilenumber, '0', '+63')
 WHEN mobilenumber ~'^9[0-9]' AND LENGTH(mobilenumber) = 10
 THEN '+63' || mobilenumber
 ELSE mobilenumber
 END

I am using MySQL 5.7. When I run the statement below, I am getting syntax error.

CASE
 WHEN mobilenumber regexp ~'^09[0-9]'
 THEN REGEXP(mobilenumber, '0', '+63')
END

Any advice please?

-------Update---------

CASE
 WHEN mobilenumber regexp ~'^09[0-9]'
 THEN REPLACE(mobilenumber, '0', '+63')
END

The problem is I am REGEXP in my THEN instead of REPLACE. I also need to remove the ~ in the WHEN statement. When I do that, all 0s are replaced by +63. Is there a way to control this?

• Rick James: REGEXP_REPLACE is available in MariaDB 10.0 and MySQL 8.0.

Answer 1

What about

...
WHEN mobilenumber REGEXP '^09[0-9]'
THEN concat('+63', substring(mobilenumber, 2))
...

? As far as I understand it, you only want the first zero to be replaced. If the pattern matches, you know that the first digit is a zero. So just simply take the substring after the first character and contact the '+63' in the beginning.