Re: Control PhoneNumber Via SQL

[tango ward <tangoward15@xxxxxxxxx>]

On Wed, May 16, 2018 at 9:04 AM, tango ward <tangoward15@xxxxxxxxx> wrote:

Okay, I figured it out.

cur_t.execute("""
                SELECT
                CASE
                  WHEN mobilenumber ~'^0[1-9]'
                  THEN regexp_replace(mobilenumber, '0', '+63')
                  ELSE mobilenumber
                END
                FROM studeprofile
                ORDER BY lastname
                """)

In my previous SELECT statement, I picked the mobilenumber before running a CASE statement to it instead of jumping directly to CASE statement after SELECT.

Sorry, just clarification for regexp_replace, is it possible to replace two character without making nested regexp_replace?

I have a phone number with the following format: 09xxxxxxxxx/09xxxxxxxxxx

cur_t.execute("""
                SELECT firstname, lastname,
                CASE
                  WHEN mobilenumber ~'^0[1-9]'
                  THEN regexp_replace(mobilenumber, '[0/0]', '+63')
                  WHEN mobilenumber ~'^9[0-9]' AND LENGTH(mobilenumber) = 10
                  THEN '+63' || mobilenumber
                  ELSE mobilenumber
                END
                FROM studeprofile
                ORDER BY lastname
                """)

I can't make the /09 to be replaced by /+63 or +63. The brackets in regex as defined https://regexr.com/, it says any of the characters inside the brackets. I think i'm missing something.