Control PhoneNumber Via SQL

Hi,

Sorry for asking question again.

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

On Tue, May 15, 2018 at 4:10 AM, tango ward <tangoward15@gmail.com> wrote:

Hi,

Sorry for asking question again.

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.

Any suggestion will be highly appreciated.

Thanks,
J

​
I don't have any code for you, if that is what you are soliciting. I did
find a couple of informative web sites which help explain how international
phone numbers are formatted. These are known as E.164 numbers.

https://support.twilio.com/hc/en-us/articles/223183008-Formatting-International-Phone-Numbers
​
https://en.wikipedia.org/wiki/List_of_country_calling_codes#Alphabetical_listing_by_country_or_region

Note that the above mainly talk about how a number is formatted, not on how
to validate that it is an actual phone number. You must trust the end user.
Which is another can of worms. Case in point -- Yesterday I got 6 automated
phone calls from the local cable company to verify some installation. The
problem is, I am not installing anything. The person either gave them a bad
number or mistyped it into a web page or the customer service rep mistyped
it.​

--
We all have skeletons in our closet.
Mine are so old, they have osteoporosis.

Maranatha! <><
John McKown

On 2018-May-15, John McKown wrote:

I don't have any code for you, if that is what you are soliciting. I did
find a couple of informative web sites which help explain how international
phone numbers are formatted. These are known as E.164 numbers.

Michael Glaesemann wrote a e.164 datatype years ago, which I later
tweaked slightly: https://github.com/alvherre/e164

I don't know its status -- not sure if it even compiles. But it might
be a useful as a starting point.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

hamann.w@t-online.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

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@raf.org> wrote:

Hi All,

Thanks for the suggestions especially the article for formatting
international phone numbers.

I also implement the suggestion of Sir 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[1]

['09xxxxxxxxx', '+639xxxxxxxxx']

The output shows 2 values in a list. I only need to get the one that starts
with country code. I can do it by using x[1]. I think this has been
explained by Sir Adrian in my previous question. I'll review that.

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

On Tuesday, May 15, 2018, tango ward <tangoward15@gmail.com> wrote:

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.

Last time you had multiple rows...this time you have multiple columns...

David J.

Did the CASE Statement produce the other columns Sir?

On Wed, May 16, 2018 at 8:53 AM, David G. Johnston <
david.g.johnston@gmail.com> 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.

On Wed, May 16, 2018 at 8:59 AM, tango ward <tangoward15@gmail.com> wrote:

Sorry for bumping this email.

I would just like to clarify regarding regexp_replace:

WHEN mobilenumber ~'^9[0-9]' AND LENGTH(mobilenumber) = 10
THEN regexp_replace(mobilenumber, '', '+63')

If the pattern is empty '', does this mean that the replacement_string
param will be added to the value of source? It does what I want it to do
but I am not sure if that's always the case if pattern param is empty.

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

On Tuesday, May 15, 2018, tango ward <tangoward15@gmail.com> wrote:

Sorry for bumping this email.

I would just like to clarify regarding regexp_replace:

WHEN mobilenumber ~'^9[0-9]' AND LENGTH(mobilenumber) = 10
THEN regexp_replace(mobilenumber, '', '+63')

If the pattern is empty '', does this mean that the replacement_string
param will be added to the value of source? It does what I want it to do
but I am not sure if that's always the case if pattern param is empty.

If you could bottom-post like the rest of us that would be helpful.

While that seems to work it is definitely obscure. You should just use
concatenation.

’+63' || mobilenumber

The pattern is a zero-length string, matched once, which first matches the
start of the input text. Not sure what happens when the input is the empty
string...

David J.

Ah yeah, the concatenation. I was thinking of using "+" but I can't make it
work and the documentation says not to use it for psycopg2.

Sorry what's bottom-post? I see you did that yesterday. Is there a format
for before making a bottom-post?

On Wed, May 16, 2018 at 11:04 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Tuesday, May 15, 2018, tango ward <tangoward15@gmail.com> wrote:

Ah yeah, the concatenation. I was thinking of using "+" but I can't make
it work and the documentation says not to use it for psycopg2.

Sorry what's bottom-post? I see you did that yesterday. Is there a format
for before making a bottom-post?

You remove quoted material that isn't relevant and then type your response
at the bottom of the email (after the stuff being quoted), like I'm doing
here.

David J.

Noted thanks Sir.

On Wed, May 16, 2018 at 11:55 AM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Wed, May 16, 2018 at 9:04 AM, tango ward <tangoward15@gmail.com> 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.