Received-SPF: pass (google.com: domain of filip.rembialkowski@gmail.com
	designates 10.42.147.199 as permitted sender)
	client-ip=10.42.147.199;
Message-ID: <4F4263F7.5050508@gmail.com>
Date: Mon, 20 Feb 2012 16:17:11 +0100
From: =?UTF-8?B?RmlsaXAgUmVtYmlhxYJrb3dza2k=?= <filip.rembialkowski@gmail.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; pl;
	rv:1.9.2.27) Gecko/20120216 Thunderbird/3.1.19
MIME-Version: 1.0
To: Andreas <maps.on@gmx.net>
CC: pgsql-sql@postgresql.org
Subject: Re: How to split up phone numbers?
References: <4F425DB9.3060604@gmx.net>
In-Reply-To: <4F425DB9.3060604@gmx.net>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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