Thread: Control PhoneNumber Via SQL
class BasePerson(TimeStampedModel):
phone_number = PhoneNumberField(max_length=50, verbose_name=_(u'phone number'), blank=True)
>> 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
>> Any suggestion will be highly appreciated.
>>
>>
>> Thanks,
>> J
>>
I would like to know if it is possible to control the phone number in SQL before inserting it to the destination DB?Sorry for asking question again.Hi,JThanks,Any suggestion will be highly appreciated.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.I have a model in Django:
class BasePerson(TimeStampedModel):
phone_number = PhoneNumberField(max_length=50, verbose_name=_(u'phone number'), blank=True)
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.
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
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.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()
['09078638001', '+639078638001']
[Finished in 0.1s]
On Wed, May 16, 2018 at 7:26 AM, <raf@raf.org> wrote:
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 especially the article for formatting international phone numbers.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]
On Wed, May 16, 2018 at 7:26 AM, <raf@raf.org> wrote:
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
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:
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.
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. cur_t.execute("""
SELECT
CASE
WHEN mobilenumber ~'^0[1-9]'
THEN regexp_replace(mobilenumber, '0', '+63')
ELSE mobilenumber
END
FROM studeprofile
ORDER BY lastname
""")
On Wed, May 16, 2018 at 8:59 AM, tango ward <tangoward15@gmail.com> wrote:
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: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.
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')
On Wed, May 16, 2018 at 9:04 AM, tango ward <tangoward15@gmail.com> wrote:
Okay, I figured it out.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.
cur_t.execute("""
SELECT
CASE
WHEN mobilenumber ~'^0[1-9]'
THEN regexp_replace(mobilenumber, '0', '+63')
ELSE mobilenumber
END
FROM studeprofile
ORDER BY lastname
""")On Wed, May 16, 2018 at 8:59 AM, tango ward <tangoward15@gmail.com> wrote: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: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.
On Tuesday, May 15, 2018, tango ward <tangoward15@gmail.com> wrote:
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.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 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: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.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 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' || mobilenumberThe 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.
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 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.
On Wed, May 16, 2018 at 9:04 AM, tango ward <tangoward15@gmail.com> wrote:
Okay, I figured it out.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.
cur_t.execute("""
SELECT
CASE
WHEN mobilenumber ~'^0[1-9]'
THEN regexp_replace(mobilenumber, '0', '+63')
ELSE mobilenumber
END
FROM studeprofile
ORDER BY lastname
""")
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
""")
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.