Re: Control PhoneNumber Via SQL

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(at)raf(dot)org> wrote:

> hamann(dot)w(at)t-online(dot)de 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
>
>
>