agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedHow to clean up phone-numbers with regex?
6+ messages / 4 participants
[nested] [flat]
* How to clean up phone-numbers with regex?
@ 2014-05-19 08:54 Andreas <maps.on@gmx.net>
0 siblings, 2 replies; 6+ messages in thread
From: Andreas @ 2014-05-19 08:54 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to clean up phone-numbers with regex?
@ 2014-05-19 13:39 Rob Sargent <robjsargent@gmail.com>
parent: Andreas <maps.on@gmx.net>
1 sibling, 1 reply; 6+ messages in thread
From: Rob Sargent @ 2014-05-19 13:39 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to clean up phone-numbers with regex?
@ 2014-05-19 15:43 Steve Crawford <scrawford@pinpointresearch.com>
parent: Andreas <maps.on@gmx.net>
1 sibling, 1 reply; 6+ messages in thread
From: Steve Crawford @ 2014-05-19 15:43 UTC (permalink / raw)
To: Andreas <maps.on@gmx.net>; pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to clean up phone-numbers with regex?
@ 2014-05-19 15:44 Steve Crawford <scrawford@pinpointresearch.com>
parent: Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Steve Crawford @ 2014-05-19 15:44 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to clean up phone-numbers with regex?
@ 2014-05-19 15:49 Rob Sargent <robjsargent@gmail.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 6+ messages in thread
From: Rob Sargent @ 2014-05-19 15:49 UTC (permalink / raw)
To: Steve Crawford <scrawford@pinpointresearch.com>; Andreas <maps.on@gmx.net>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: How to clean up phone-numbers with regex?
@ 2014-05-19 15:52 David G Johnston <david.g.johnston@gmail.com>
parent: Steve Crawford <scrawford@pinpointresearch.com>
0 siblings, 0 replies; 6+ messages in thread
From: David G Johnston @ 2014-05-19 15:52 UTC (permalink / raw)
To: pgsql-sql
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.h...
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
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2014-05-19 15:52 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2014-05-19 08:54 How to clean up phone-numbers with regex? Andreas <maps.on@gmx.net>
2014-05-19 13:39 ` Rob Sargent <robjsargent@gmail.com>
2014-05-19 15:44 ` Steve Crawford <scrawford@pinpointresearch.com>
2014-05-19 15:49 ` Rob Sargent <robjsargent@gmail.com>
2014-05-19 15:43 ` Steve Crawford <scrawford@pinpointresearch.com>
2014-05-19 15:52 ` David G Johnston <david.g.johnston@gmail.com>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox