Re: Control PhoneNumber Via SQL

[tango ward <tangoward15@xxxxxxxxx>]

Hi All,

Thanks for the suggestions. I really appreciate it specially the article of formatting international phone numbers.

I also tried implementing the suggestion of Wolfgang:

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

print x

Output:
['09078638001', '+639078638001']
[Finished in 0.1s]

I can access the index 1 of the output list to get the +639078638001. I think this has been explained already by Sir Adrian in my previous question about the about being shown as list. I'll review that.

On Wed, May 16, 2018 at 7:26 AM, <raf@xxxxxxx> wrote:

hamann.w@xxxxxxxxxxx wrote:

> >> Hi,
> >>
> >> I would like to know if it is possible to control the phone number in SQL
> >> before inserting it to the destination DB?
> >>
> >> I have a model in Django:
> >>
> >> class BasePerson(TimeStampedModel):
> >>  phone_number = PhoneNumberField(max_length=50, verbose_name=_(u'phone
> >> number'), blank=True)
> >>
> >> The data for phone number that I am migrating doesn't have country code. I
> >> want to determine first if the number has country code in it, if it doesn't
> >> then I will add the country code on the number before INSERTING it to the
> >> destination database.
> >>
> Hi, something like
> insert into newtable (phone, ...)
>  select case when phone ~ '^0[1-9]' then regex_replace('0', '+49', phone) else
>   case when phone ~ '^00' then regex_replace('00', '+', phone) else phone end end, ...
>  from oldtable;
>
> Regards
> Wolfgang

it might be better to ask this on a django forum since it sounds
like you want django's orm to handle this. you probably just
need to subclass PhoneNumberField so its constructor will
reformat whatever is given to it as the phone number to be
inserted. maybe you need a Manager class for the model (probably
not). maybe you just need a function that takes the default
country code or country dialling code and the phone number and
returns what you want to insert and then always use its return
value when assigning a value to the phone_number field. i
suspect that subclassing PhoneNumberField is probably the best
approach.

cheers,
raf