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?
REGEXP_REPLACEis available in MariaDB 10.0 and MySQL 8.0.