From maps.on@gmx.net Mon May 19 08:54:31 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) 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 ) 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 ) 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 ) 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 ; 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 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: 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. --------------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 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-- From robjsargent@gmail.com Mon May 19 13:40:15 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) 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 ) 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 ) 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 ) 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 ; 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 (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" X-Mailer: iPhone Mail (11D201) From: Rob Sargent Subject: Re: How to clean up phone-numbers with regex? Date: Mon, 19 May 2014 07:39:58 -0600 To: Andreas X-Pg-Spam-Score: -2.0 (--) 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 --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 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

Have you looked into regular expressions?

Sent from my iPhone

On May 19, 2014, at 2:54 AM, Andreas <maps.on@gmx.net> 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/()'?

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

--Apple-Mail-0F97447E-A83E-4118-9F39-EA963F606F0A-- From scrawford@pinpointresearch.com Mon May 19 15:43:59 2014 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-- From scrawford@pinpointresearch.com Mon May 19 15:44:44 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPk4-0004pQ-1i for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 15:44:44 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WmPk3-0007FC-Fj for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 15:44:43 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPk2-0007E5-8H for pgsql-sql@postgresql.org; Mon, 19 May 2014 15:44:42 +0000 Received: from cerberus.pinpointresearch.com ([66.7.238.130] helo=polaris.pinpointresearch.com) by magus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPjz-0006xg-Rw for pgsql-sql@postgresql.org; Mon, 19 May 2014 15:44:41 +0000 Received: from [192.168.1.179] (betelgeuse.pinpointresearch.com [192.168.1.179]) by polaris.pinpointresearch.com (Postfix) with ESMTP id 74669E00EC83; Mon, 19 May 2014 08:44:38 -0700 (PDT) Message-ID: <537A26E6.6020501@pinpointresearch.com> Date: Mon, 19 May 2014 08:44:38 -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: Rob Sargent , Andreas CC: "pgsql-sql@postgresql.org" Subject: Re: How to clean up phone-numbers with regex? References: <5379C6C1.5020904@gmx.net> <255A9A75-C8A3-4AE6-91E4-1819BE37DC27@gmail.com> In-Reply-To: <255A9A75-C8A3-4AE6-91E4-1819BE37DC27@gmail.com> Content-Type: multipart/alternative; boundary="------------080108020100010606070801" 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. --------------080108020100010606070801 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 05/19/2014 06:39 AM, Rob Sargent wrote: > Have you looked into regular expressions? > I think his subject line answered that question... Cheers, Steve --------------080108020100010606070801 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 7bit

On 05/19/2014 06:39 AM, Rob Sargent wrote:

Have you looked into regular expressions?

I think his subject line answered that question...

Cheers,
Steve

--------------080108020100010606070801-- From robjsargent@gmail.com Mon May 19 15:49:47 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPox-00050M-Jr for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 15:49:47 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WmPow-0001wj-Ar for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 15:49:46 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPov-0001wc-76 for pgsql-sql@postgresql.org; Mon, 19 May 2014 15:49:45 +0000 Received: from mail-ig0-x234.google.com ([2607:f8b0:4001:c05::234]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPol-000480-4r for pgsql-sql@postgresql.org; Mon, 19 May 2014 15:49:44 +0000 Received: by mail-ig0-f180.google.com with SMTP id c1so3709206igq.7 for ; Mon, 19 May 2014 08:49:34 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=message-id:date:from:user-agent:mime-version:to:cc:subject :references:in-reply-to:content-type; bh=0+1K4/qcJTozwqmYXukRMI4XyRrSlM9JukRwe65uNoU=; b=GAByV/OMzRAn/QwbKGEKEk1XD/YWrsMaTLJeszcXGxhDhpTEC1P7Uh7Ewl3khlVy6e Gd/drCeK4eTtRr/6DVSINKnVtF1CcdEhH8SfO45bh51XsKFhytLY4obskmSF+j4w53sG Ty4xrkkvtA1QCTMgQb+sB7GxypVoBi8nTxaPi6kxLt7Eq6bitpmHyOi4TsHny/ffPDo3 sLLtS6k5iLv1tn/rrNvQ+lPX2nk6iIIhTr1CutF3rsLlCUCGIralHQRdeasdodrT5JYm b9FiGxYRL1cbHYKsnRjcbUnpHX/Hsq3Vo/Md+me4CMCZG8vVXAQ+Z5m+083yH5hlAGn1 3Acw== X-Received: by 10.50.112.68 with SMTP id io4mr2183953igb.5.1400514574414; Mon, 19 May 2014 08:49:34 -0700 (PDT) Received: from stability.med.utah.edu (stability.med.utah.edu. [155.100.158.97]) by mx.google.com with ESMTPSA id kw1sm21824403igb.4.2014.05.19.08.49.32 for (version=TLSv1 cipher=ECDHE-RSA-RC4-SHA bits=128/128); Mon, 19 May 2014 08:49:33 -0700 (PDT) Message-ID: <537A280C.407@gmail.com> Date: Mon, 19 May 2014 09:49:32 -0600 From: Rob Sargent User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:24.0) Gecko/20100101 Thunderbird/24.5.0 MIME-Version: 1.0 To: Steve Crawford , Andreas CC: "pgsql-sql@postgresql.org" Subject: Re: How to clean up phone-numbers with regex? References: <5379C6C1.5020904@gmx.net> <255A9A75-C8A3-4AE6-91E4-1819BE37DC27@gmail.com> <537A26E6.6020501@pinpointresearch.com> In-Reply-To: <537A26E6.6020501@pinpointresearch.com> Content-Type: multipart/alternative; boundary="------------060305080102030908050805" X-Pg-Spam-Score: -2.0 (--) 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. --------------060305080102030908050805 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit On 05/19/2014 09:44 AM, Steve Crawford wrote: > On 05/19/2014 06:39 AM, Rob Sargent wrote: >> Have you looked into regular expressions? >> > I think his subject line answered that question... > > Cheers, > Steve > You're right. Apologies. --------------060305080102030908050805 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit

On 05/19/2014 09:44 AM, Steve Crawford wrote:

On 05/19/2014 06:39 AM, Rob Sargent wrote:

Have you looked into regular expressions?

I think his subject line answered that question...

Cheers,
Steve

You're right. Apologies.
--------------060305080102030908050805-- From david.g.johnston@gmail.com Mon May 19 15:52:16 2014 Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPrM-000581-FE for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 15:52:16 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1WmPrL-00038K-Pr for pgsql-sql@arkaria.postgresql.org; Mon, 19 May 2014 15:52:15 +0000 Received: from makus.postgresql.org ([2001:4800:1501:1::229]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPrL-00038C-27 for pgsql-sql@postgresql.org; Mon, 19 May 2014 15:52:15 +0000 Received: from sam.nabble.com ([216.139.236.26]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1WmPrJ-0004DI-CE for pgsql-sql@postgresql.org; Mon, 19 May 2014 15:52:14 +0000 Received: from [192.168.236.26] (helo=sam.nabble.com) by sam.nabble.com with esmtp (Exim 4.72) (envelope-from ) id 1WmPrI-0006gl-CV for pgsql-sql@postgresql.org; Mon, 19 May 2014 08:52:12 -0700 Date: Mon, 19 May 2014 08:52:12 -0700 (PDT) From: David G Johnston To: pgsql-sql@postgresql.org Message-ID: <1400514732379-5804493.post@n5.nabble.com> In-Reply-To: <537A26B5.3030309@pinpointresearch.com> References: <5379C6C1.5020904@gmx.net> <537A26B5.3030309@pinpointresearch.com> Subject: Re: How to clean up phone-numbers with regex? MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: 3.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 Steve Crawford wrote > On 05/19/2014 01:54 AM, Andreas wrote: > >> >> 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 Actually, section "9.7.3. POSIX Regular Expressions" - specifically table 9-11 at the beginning of that section - is the most common way to perform the tests in a where clause. regexp_matches(...) is for when you want to extract data. David J. -- View this message in context: http://postgresql.1045698.n5.nabble.com/How-to-clean-up-phone-numbers-with-regex-tp5804450p5804493.html Sent from the PostgreSQL - sql mailing list archive at Nabble.com. -- Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql