pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to split up phone numbers?
4+ messages / 4 participants
[nested] [flat]
* How to split up phone numbers?
@ 2012-02-20 14:50 Andreas <maps.on@gmx.net>
2012-02-20 15:17 ` Re: How to split up phone numbers? Filip Rembiałkowski <filip.rembialkowski@gmail.com>
2012-02-21 21:40 ` Re: How to split up phone numbers? Tim Landscheidt <tim@tim-landscheidt.de>
2012-02-23 09:14 ` Re: How to split up phone numbers? Jasen Betts <jasen@xnet.co.nz>
0 siblings, 3 replies; 4+ messages in thread
From: Andreas @ 2012-02-20 14:50 UTC (permalink / raw)
To: pgsql-sql
Hi,
is there a way to split up phone numbers?
I know that's a tricky topic and it depends on the national phone number
format.
I'm especially interested in a solution for Germany, Swizerland and Austria.
I've got everything in a phone number column that makes hardly sense like:
+49432156780
0049 4321 5678 0
04321/5678-0
and so on...
Those 3 samples are actually the same number in different notations.
Aim would be to get a normalized number split up in 4 seperate columns
nr_nation
nr_city
nr_main
nr_individual
so I end up with
49 4321 5678 0 for central
49 4321 5678 42 for Mr. Smith
Is this doable?
It would be a start to at least split off nr_nation and nr_city.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to split up phone numbers?
2012-02-20 14:50 How to split up phone numbers? Andreas <maps.on@gmx.net>
@ 2012-02-20 15:17 ` Filip Rembiałkowski <filip.rembialkowski@gmail.com>
2 siblings, 0 replies; 4+ messages in thread
From: Filip Rembiałkowski @ 2012-02-20 15:17 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
At 2012-02-20 15:50, Andreas wrote:
> Hi,
> is there a way to split up phone numbers?
> I know that's a tricky topic and it depends on the national phone
> number format.
> I'm especially interested in a solution for Germany, Swizerland and
> Austria.
>
> I've got everything in a phone number column that makes hardly sense
> like:
> +49432156780
> 0049 4321 5678 0
> 04321/5678-0
> and so on...
> Those 3 samples are actually the same number in different notations.
>
> Aim would be to get a normalized number split up in 4 seperate columns
> nr_nation
> nr_city
> nr_main
> nr_individual
>
> so I end up with
> 49 4321 5678 0 for central
> 49 4321 5678 42 for Mr. Smith
>
> Is this doable?
>
yes, sure - with regular expressions and/or with bunch of IF/THEN/ELSE
blocks in PL/PgSQL.
see regexp_split_to_array, and other regex functions in the manual:
http://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-POSIX-REGEXP
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to split up phone numbers?
2012-02-20 14:50 How to split up phone numbers? Andreas <maps.on@gmx.net>
@ 2012-02-21 21:40 ` Tim Landscheidt <tim@tim-landscheidt.de>
2 siblings, 0 replies; 4+ messages in thread
From: Tim Landscheidt @ 2012-02-21 21:40 UTC (permalink / raw)
To: pgsql-sql
(anonymous) wrote:
> is there a way to split up phone numbers?
> I know that's a tricky topic and it depends on the national
> phone number format.
> I'm especially interested in a solution for Germany, Swizerland and Austria.
> I've got everything in a phone number column that makes hardly sense like:
> +49432156780
> 0049 4321 5678 0
> 04321/5678-0
> and so on...
> Those 3 samples are actually the same number in different notations.
> Aim would be to get a normalized number split up in 4 seperate columns
> nr_nation
> nr_city
> nr_main
> nr_individual
> so I end up with
> 49 4321 5678 0 for central
> 49 4321 5678 42 for Mr. Smith
> Is this doable?
> It would be a start to at least split off nr_nation and nr_city.
I seem to faintly remember that there are some anomalies in
the German area codes, i. e. "+49 xy" may be city A, with
"+49 xyz" being city B. Obviously, you will also not be
able to differentiate between normal and extension numbers
if they aren't properly marked up in the input.
Tim
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: How to split up phone numbers?
2012-02-20 14:50 How to split up phone numbers? Andreas <maps.on@gmx.net>
@ 2012-02-23 09:14 ` Jasen Betts <jasen@xnet.co.nz>
2 siblings, 0 replies; 4+ messages in thread
From: Jasen Betts @ 2012-02-23 09:14 UTC (permalink / raw)
To: pgsql-sql
On 2012-02-20, Andreas <maps.on@gmx.net> wrote:
> Hi,
> is there a way to split up phone numbers?
several. I wouldn't trust a computer to do any of them.
> I know that's a tricky topic and it depends on the national phone number
> format.
> I'm especially interested in a solution for Germany, Swizerland and Austria.
>
> I've got everything in a phone number column that makes hardly sense like:
> +49432156780
> 0049 4321 5678 0
> 04321/5678-0
> and so on...
> Those 3 samples are actually the same number in different notations.
normalise them.
push them all towards the first format. the rules are relatively
simple.
now if you want to split that into parts that means something you can
probably get the gorey details from the phone company. but if you
just want do do it for display split it wherever you like.
--
⚂⚃ 100% natural
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2012-02-23 09:14 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2012-02-20 14:50 How to split up phone numbers? Andreas <maps.on@gmx.net>
2012-02-20 15:17 ` Filip Rembiałkowski <filip.rembialkowski@gmail.com>
2012-02-21 21:40 ` Tim Landscheidt <tim@tim-landscheidt.de>
2012-02-23 09:14 ` Jasen Betts <jasen@xnet.co.nz>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox