Re: How to clean up phone-numbers with regex?

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