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?
Try this :
select substring(name FROM '[0-9]+') from contacts
select substring('998343913-15' from '[0-9]+') I get 998343913 and not 99834391315select 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;
SELECT regexp_replace(name, '\D', '', 'g') FROM contacts;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
