SQL & PL/SQL

There are many other ways to do this.
Here is just one:
Travel across the string variable using instr, evaluate every position comparing it to a number (you can use the ascii code if you prefer) and if it is a number you can assign the value of that position to a variable which is holding the result set.

Regards,
Max.

with test as (select column_value col from table
(sys.ODCIVarchar2List('2222jhone','123344Sam','11Tim')))
SELECT REGEXP_REPLACE(col, '[[:alpha:]]','')
FROM test

The important part is REGEXP_REPLACE and [[:alpha:]]

try this

select regexp_replace('12345TTTT', '[A-Za-z]') from dual

also possible


select regexp_replace('12345TTTT', '[^[:digit:]]') from dual;

Your regular expression:
SQL> SELECT REGEXP_REPLACE('max1a23b56','[[:alpha:]]') from dual
2 /

REGEX
-----
12356

Regards,
Max.

one option

with t as
  2       (select '2222jhone' str
  3          from dual
  4        union all
  5        select '123344Sam' str
  6          from dual
  7        union all
  8        select '11Tim' str
  9          from dual)
 10  select regexp_substr(str,'[[:digit:]]*')
 11    from t;

REGEXP_SU
---------
2222
123344
11

Hi,

Thanks to all for prompt responce...All are working fine.

Hi,

I am at oralce 9i,
Oracle9i Enterprise Edition Release 9.2.0.8.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.8.0 - Production

When I executed the solution, it gave me error


SQL> select regexp_replace('12345TTTT', '[A-Za-zfrom dual
2 /
select regexp_replace('12345TTTT', '[A-Za-zfrom dual
*
ERROR at line 1:
ORA-00904: "REGEXP_REPLACE": invalid identifier

Any solution for this?

SQL> ed
Wrote file afiedt.buf

  1* SELECT REPLACE(TRANSLATE(UPPER('12345abcdABC12345'),'ABCDEFGHIJKLMNOPQRSTUVWXYZ',' '),' ','') FROM DUAL
SQL> /

REPLACE(TR
----------
1234512345

SQL> 

Message was edited by:
user605919

It surprises me that, yet again, this question has been asked.

Just putting the exact same subject of this thread into the search engine gives loads of results with answers for how to do this already...

http://forums.oracle.com/forums/search.jspa?threadID=&q=extract+number+value+from+string&objID=f75&dateRange=all&userID=&numResults=30

I guess people are just too lazy to search.

The very first post of which leads right back here....

Numpty. :)

It's search results, so of course this thread is in there. Doh!

You NEVER want to use REGEXP_REPLACE when looking to query fields with numbers only because you are actually changing the data by removing or replacing with something that was not originally in the dataset. Use REGEXP_LIKE - SEE BELOW.


SELECT <FieldNames>
WHERE REGEXP_LIKE(<FieldName>, '^[[:digit:]]+$')

--This will only return fields that contain NUMBERS ONLY.

using a TRANSLATE function.

SQL> select trim(translate(str,translate(str,'1234567890',' '),' ')) number_char
  2    from (select '2222jhone' str from dual union all
  3          select '123344Sam' str from dual union all
  4          select '11Tim'     str from dual);

NUMBER_CHAR
-----------
2222
123344
11

SQL> 

Please don't drag up 4 year old threads.

locking this thread