0

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?

1
  • REGEXP_REPLACE is available in MariaDB 10.0 and MySQL 8.0. Commented May 28, 2018 at 14:36

1 Answer 1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.