Received: from malur.postgresql.org ([217.196.149.56])
 by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from
 <pgsql-sql-owner+M37759=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1WmNnb-0000DM-3Q
 for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 13:40:15 +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+M37759=pgsql+2Dsql=arkaria.postgresql.org@postgresql.org>)
 id 1WmNnZ-0007OY-L4
 for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 13:40:13 +0000
Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29])
 by malur.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <robjsargent@gmail.com>) id 1WmNnY-0007OP-TM
 for pgsql-sql@postgresql.org; Mon, 19 May 2014 13:40:12 +0000
Received: from mail-ob0-x235.google.com ([2607:f8b0:4003:c01::235])
 by magus.postgresql.org with esmtp (Exim 4.80)
 (envelope-from <robjsargent@gmail.com>) id 1WmNnU-0004X0-Os
 for pgsql-sql@postgresql.org; Mon, 19 May 2014 13:40:12 +0000
Received: by mail-ob0-f181.google.com with SMTP id wm4so6206112obc.12
 for <pgsql-sql@postgresql.org>; Mon, 19 May 2014 06:40:06 -0700 (PDT)
DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113;
 h=references:mime-version:in-reply-to:content-type
 :content-transfer-encoding:message-id:cc:from:subject:date:to;
 bh=f8aCfgTHISEZzxCWzWFdeH+WnC3qLo2F4r/7iA9mfxI=;
 b=rd5O3ccfztfUfs9z99FQ98Iw1xzNPuTHKXeJ62Q6D7yKstwH/ZMalpGD8mBEB1IgYd
 9qraee1zkKR8FWUarFs1MMjLZ9X2OtNEgODBH2L/ktR92DtnGm6MPdLoLIB6pdJB88DZ
 7tepClSOqQDulr9BwOAeT6Bxtixzy8sRJ2VOvxiEL13Or9SBSt07P3k29db0SGrPul8+
 D0ElXy8ZiZnCENoo0hUxSFZB9VApIsiJQA+Cou+mg2+eXN3xCostt9NfMHd/mItl62FX
 2YgBVM5H6Z7rZQuXtvqi2Esig7BTM0UBhDdnz79xcc4qIQSobyxjjfwoELjQDT8RxdBz
 /AEw==
X-Received: by 10.182.97.1 with SMTP id dw1mr36320842obb.23.1400506806813;
 Mon, 19 May 2014 06:40:06 -0700 (PDT)
Received: from [10.240.164.121] (96.sub-174-251-16.myvzw.com. [174.251.16.96])
 by mx.google.com with ESMTPSA id
 ub1sm37551499oeb.9.2014.05.19.06.39.59 for <multiple recipients>
 (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128);
 Mon, 19 May 2014 06:40:00 -0700 (PDT)
References: <5379C6C1.5020904@gmx.net>
Mime-Version: 1.0 (1.0)
In-Reply-To: <5379C6C1.5020904@gmx.net>
Content-Type: multipart/alternative;
 boundary=Apple-Mail-0F97447E-A83E-4118-9F39-EA963F606F0A
Content-Transfer-Encoding: 7bit
Message-Id: <255A9A75-C8A3-4AE6-91E4-1819BE37DC27@gmail.com>
Cc: "pgsql-sql@postgresql.org" <pgsql-sql@postgresql.org>
X-Mailer: iPhone Mail (11D201)
From: Rob Sargent <robjsargent@gmail.com>
Subject: Re: How to clean up phone-numbers with regex?
Date: Mon, 19 May 2014 07:39:58 -0600
To: Andreas <maps.on@gmx.net>
X-Pg-Spam-Score: -2.0 (--)
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


--Apple-Mail-0F97447E-A83E-4118-9F39-EA963F606F0A
Content-Type: text/plain;
	charset=us-ascii
Content-Transfer-Encoding: quoted-printable

Have you looked into regular expressions?

Sent from my iPhone

> On May 19, 2014, at 2:54 AM, Andreas <maps.on@gmx.net> wrote:
>=20
> Hi
>=20
> I need to clean up phone-numbers. Somehow I got a Excel list that has weir=
d 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.
>=20
> OK, I know how to read the stuff into a temporary table to clean it up bef=
ore the actual import.
> How can I do an update on the column that deletes every char that is not i=
n a given set of chars like '+- 0123456/()'?
>=20
>=20
> Second but similar question:
> How can I select records that have fields that contain characters       no=
t included in a given alphabet?
> E.G. find fields that contain some char not in 0-9,a-z,A-Z, +-()/?
>=20
>=20
> regards
> Andreas

--Apple-Mail-0F97447E-A83E-4118-9F39-EA963F606F0A
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 dir="auto"><div>Have you looked into regular expressions?<br><br>Sent from my iPhone</div><div><br>On May 19, 2014, at 2:54 AM, Andreas &lt;<a href="mailto:maps.on@gmx.net">maps.on@gmx.net</a>&gt; wrote:<br><br></div><blockquote type="cite"><div>
  

    <meta http-equiv="content-type" content="text/html; charset=UTF-8">
  
  
    <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>
  

</div></blockquote></body></html>
--Apple-Mail-0F97447E-A83E-4118-9F39-EA963F606F0A--