Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37758=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1WmJL5-0006Eg-Bj
 for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 08:54:31 +0000
Received: from localhost ([127.0.0.1] helo=postgresql.org)
 by malur.postgresql.org with smtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37758=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1WmJL4-0001qY-RO
 for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 08:54:30 +0000
Received: from makus.postgresql.org ([2001:4800:1501:1::229])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <maps.on@gmx.net>) id 1WmJL4-0001qS-1F
 for pgsql-sql@postgresql.org; Mon, 19 May 2014 08:54:30 +0000
Received: from mout.gmx.net ([212.227.17.20])
 by makus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <maps.on@gmx.net>) id 1WmJL1-0004gg-LH
 for pgsql-sql@postgresql.org; Mon, 19 May 2014 08:54:29 +0000
Received: from [192.168.1.113] ([88.130.55.37]) by mail.gmx.com (mrgmx002)
 with ESMTPSA (Nemesis) id 0Lj1Xa-1XKp4u0p38-00dEvu for
 <pgsql-sql@postgresql.org>; Mon, 19 May 2014 10:54:26 +0200
Message-ID: <5379C6C1.5020904@gmx.net>
Date: Mon, 19 May 2014 10:54:25 +0200
From: Andreas <maps.on@gmx.net>
User-Agent: Mozilla/5.0 (Windows NT 5.1;
 rv:24.0) Gecko/20100101 Thunderbird/24.5.0
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Subject: How to clean up phone-numbers with regex?
Content-Type: multipart/alternative;
 boundary="------------070508040006000802030600"
X-Provags-ID: V03:K0:+hxz3xDx3JbZjVHs1n0wkr6Db9K5Y0x38F2Cog0fLeWqqA3HT4I
 2xgLnqQ6myitlu6t0S8/2mzIJKVMHAKXN9eTNJyLTzR0g3kSvGrhAUGWS4nJ4YLMxu2iuOH
 umWX1gTHP/OQz9YNHCNSn8/dQhg6Sb8CZulKssG3wLQgSHs1pXGLJeQYa79qZteoW/DI5wr
 BMcQUQv1SjpRLMYtAIoIQ==
X-Pg-Spam-Score: 0.1 (/)
List-Archive: <http://archives.postgresql.org/pgsql-sql>
List-Help: <mailto:majordomo@postgresql.org?body=help>
List-ID: <pgsql-sql.postgresql.org>
List-Owner: <mailto:pgsql-sql-owner@postgresql.org>
List-Post: <mailto:pgsql-sql@postgresql.org>
List-Subscribe: <mailto:majordomo@postgresql.org?body=sub%20pgsql-sql>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
X-Mailing-List: pgsql-sql
Precedence: bulk
Sender: pgsql-sql-owner@postgresql.org

This is a multi-part message in MIME format.
--------------070508040006000802030600
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 7bit

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/()'?


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, +-()/?


regards
Andreas

--------------070508040006000802030600
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 7bit

<html>
  <head>

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  </head>
  <body text="#000000" bgcolor="#FFFFFF">
    <font face="Arial">Hi<br>
      <br>
      I need to clean up phone-numbers. Somehow I got a Excel list that
      has weird graphical characters trailing some of the entries.</font><br>
    <font face="Arial"><font face="Arial">My DB is UTF8 so it would
        store this mess but I don<small>'t like to import it in the
          first place.<br>
          <br>
          OK, I know how to read the stuff into a temporary table to
          clean it up before the actual import.<br>
        </small></font>How can I do an update on the column that deletes
      every char that is not in a given set of chars like '+-
      0123456/()'?<br>
      <br>
      <small></small><br>
      Second but similar question:<br>
      How can I select records that have fields that contain characters
      not included in a given alphabet?<br>
      E.G. find fields that contain some char not in 0-9,a-z,A-Z, +-()/?<br>
      <br>
      <br>
      regards<br>
      Andreas<br>
    </font>
  </body>
</html>

--------------070508040006000802030600--