Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPjL-0004oS-A6 for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 15:43:59 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WmPjK-0006cl-Go for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 15:43:58 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPjI-0006cc-Ac for pgsql-sql@postgresql.org; Mon, 19 May 2014 15:43:56 +0000 Received: from cerberus.pinpointresearch.com ([66.7.238.130] helo=polaris.pinpointresearch.com) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPjC-00040k-QY for pgsql-sql@postgresql.org; Mon, 19 May 2014 15:43:55 +0000 Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id A2310E00EC83; Mon, 19 May 2014 08:43:49 -0700 (PDT) Message-ID: <537A26B5.3030309@pinpointresearch.com> Date: Mon, 19 May 2014 08:43:49 -0700 From: Steve Crawford User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.5.0 MIME-Version: 1.0 To: Andreas , pgsql-sql@postgresql.org Subject: Re: How to clean up phone-numbers with regex? References: <5379C6C1.5020904@gmx.net> In-Reply-To: <5379C6C1.5020904@gmx.net> Content-Type: multipart/alternative; boundary="------------010809040002090306030103" X-Pg-Spam-Score: -2.5 (--) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org This is a multi-part message in MIME format. --------------010809040002090306030103 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 05/19/2014 01:54 AM, Andreas wrote: > Hi > > I need to clean up phone-numbers. Somehow I got a Excel list that has > weird graphical characters trailing some of the entries. > My DB is UTF8 so it would store this mess but I don't like to import > it in the first place. > > OK, I know how to read the stuff into a temporary table to clean it up > before the actual import. > How can I do an update on the column that deletes every char that is > not in a given set of chars like '+- 0123456/()'? > See: http://www.postgresql.org/docs/current/static/functions-matching.html For the first case, the regexp_replace function is probably your best bet. But note that, depending on the quality of your input, just removing characters outside that range may still not yield the desired result. select regexp_replace('(12s3)-456-635/6(a+sdk', '[^0-9()+-/]', '', 'g'); regexp_replace ------------------- (123)-456-635/6(+ You can remove all formatting by requiring only digits then check and/or reformat later as desired. steve=> select regexp_replace('(12s3)-456-6356(a+sdk', '[^0-9]', '', 'g'); regexp_replace ---------------- 1234566356 > > Second but similar question: > How can I select records that have fields that contain characters not > included in a given alphabet? > E.G. find fields that contain some char not in 0-9,a-z,A-Z, +-()/? > See regexp_match on the above-referenced page. Cheers, Steve --------------010809040002090306030103 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

On 05/19/2014 01:54 AM, Andreas wrote:=

Hi

I need to clean up phone-numbers. Somehow I got a Excel list that has weird graphical characters trailing some of the entries.
My DB is UTF8 so it would store this mess but I don't like to import it in the first place.

OK, I know how to read the stuff into a temporary table to clean it up before the actual import.
How can I do an update on the column that deletes every char that is not in a given set of chars like '+- 0123456/()'?

See: http://www.postgresql.org/do= cs/current/static/functions-matching.html

For the first case, the regexp_replace function is probably your best bet. But note that, depending on the quality of your input, just removing characters outside that range may still not yield the desired result.

select regexp_replace('(12s3)-456-635/6(a+sdk', '[^0-9()+-/]', '', 'g');
=C2=A0 regexp_replace=C2=A0=C2=A0
-------------------
=C2=A0(123)-456-635/6(+

You can remove all formatting by requiring only digits then check and/or reformat later as desired.
steve=3D> select regexp_replace('(12s3)-456-6356(a+sdk', '[^0-9]', '', 'g');
=C2=A0regexp_replace
----------------
=C2=A01234566356

Second but similar question:
How can I select records that have fields that contain characters not included in a given alphabet?
E.G. find fields that contain some char not in 0-9,a-z,A-Z, +-()/?

See regexp_match on the above-referenced page.

Cheers,
Steve

--------------010809040002090306030103--