Thread: How to split up phone numbers?
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.
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
(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
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