Simply:
SELECT NULLIF(regexp_replace(po_number, '\D','','g'), '')::numeric AS result
FROM tbl;
\D being the class shorthand for “not a digit”.
And you need the 4th parameter 'g' (for “globally”) to replace all occurrences.
Details in the manual.
For a known, limited set of characters to replace, plain string manipulation functions like replace() or translate() are substantially cheaper. Regular expressions are just more versatile, and we want to eliminate everything but digits in this case. Related:
- Regex remove all occurrences of multiple characters in a string
- PostgreSQL SELECT only alpha characters on a row
- Is there a regexp_replace equivalent for postgresql 7.4?
But why Postgres 8.4? Consider upgrading to a modern version.
Consider pitfalls for outdated versions:
- Order varchar string as numeric
- WARNING: nonstandard use of escape in a string literal