CODEWITHSUNDEEP
sqlpostgresqlpattern-matching
Luigi
Luigi

Reputation: 5603

Return only numeric values from column - Postgresql

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?

Upvotes: 16

Views: 36704

Answers (3)

Nouman Bhatti
Nouman Bhatti

Reputation: 1421

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

Upvotes: 3

peter.petrov
peter.petrov

Reputation: 39477

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;

Upvotes: 14

Rida BENHAMMANE
Rida BENHAMMANE

Reputation: 4129

Try this :

select substring(name FROM '[0-9]+') from contacts

Upvotes: 18

Related Questions