On 02/20/2012 08:49 AM, 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? It would be a start to at least split off nr_nation and nr_city.
I would do it in multiple passes. Trim everything out (spaces, slashes, etc) to try and get a constant number, then use the length to determin the different types of numbers, then use substring to pull out the parts. -- for shorter phone numbers select '49' as nr_nation, substring(phone from 1 for 4) as nr_city, etc... where length( regexp_replace(phone, '[^\d]', '', 'g') ) = 10 and nr_nation is null; -- for longer phone numbers select substring(phone from 1 for 2) as nr_nation, substring(phone from 3 for 4) as nr_city, etc... where length( regexp_replace(phone, '[^\d]', '', 'g') ) = 12 and nr_nation is null; -- etc -Andy -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
