Return only numeric values from column - Postgresql

Question

I have several values in my name column within the contacts table similar to this one:

test 3100509 DEMO NPS

I want to return only the numeric piece of each value from name.

I tried this:

select substring(name FROM '^[0-9]+|.*') from contacts

But that doesn't do it.

Any thoughts on how to strip all characters that are not numeric from the returned values?

Answer 1

If you want to extract the numeric values with decimal point than use this

select NULLIF(regexp_replace(name, '[^0-9.]*','','g'), '')::numeric from contacts

Answer 2

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

This should do it. It will work even if you have more than one numeric sequences in the name.

Example:

create table contacts(id int, name varchar(200));

insert into contacts(id, name) values(1, 'abc 123 cde 555 mmm 999');

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