current community

your communities

more stack exchange communities

Stack Exchange
tour help
careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question

2 Answers 2

up vote 1 down vote accepted

Try this :

select substring(name FROM '[0-9]+') from contacts
share|improve this answer
up vote 2 down vote

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;
share|improve this answer
    
+1 And maybe even with a regexp class shorthand: SELECT regexp_replace(name, '\D', '', 'g') FROM contacts; –  Erwin Brandstetter Feb 22 at 6:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.