Reputation: 5603
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
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
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
Reputation: 4129
Try this :
select substring(name FROM '[0-9]+') from contacts
Upvotes: 18