select only numbers from field

[xenology]

Is there a postgres function I can use in a SELECT statement that will strip out non-numeric characters from a field?

I only want numbers from the field returned. i.e. say its a phone number field. (555)123-4567 should return 5551234567 and 1-800-petmeds would return 1800

Thanks in advance!

 

[xenology]

I figured it out...... In case anyone else needs to do it...


SELECT REPLACE(TRANSLATE(field_name,
REPLACE(TRANSLATE(field_name,
'0123456789',
RPAD('#',LENGTH(field_name),'#')),'#',''),
RPAD('#',LENGTH(field_name),'#')),'#','') AS foo,*
FROM table_name

 

[stefanwagner]

select regexp_replace(name, '[^0-9]', '', 'g') from foo;

[^0-9]: anything but 0-9
'' replace with
'g' globally (not just the first pattern).
Source:
file:///usr/share/doc/postgresql-doc-8.1/html/functions-string.html
file:///usr/share/doc/postgresql-doc-8.1/html/functions-matching.html

don't visit my homepage:

http://home.arcor.de/hirnstrom/bewerbung