Extract numbers from a field in PostgreSQL

Simply:

SELECT NULLIF(regexp_replace(po_number, '\D','','g'), '')::numeric AS resultFROM   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.

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

I think you want something like this:

select (case when regexp_replace(po_number, '[^\w],.-+\?/', '') ~ '^[0-9]+$'             then regexp_replace(po_number, '[^\w],.-+\?/', '')::numeric        end) as po_number_new from test;

That is, you need to do the conversion on the string after replacement.

Note: This assumes that the "number" is just a string of digits.

The logic I would use to determine if the po_number field contains numeric digits is that its length should decrease when attempting to remove numeric digits.

If so, then all non numeric digits ([^\d]) should be removed from the po_number column. Otherwise, NULL should be returned.

select case when char_length(regexp_replace(po_number, '\d', '', 'g')) < char_length(po_number)            then regexp_replace(po_number, '[^0-9]', '', 'g')            else null       end as po_number_newfrom test