How to get integer from column without using regexp_like

Question

I have a Column X in Oracle that has values like "a1b2c3", "abc", "1ab", "123", "156-346"

how do I write a sql query that returns me only the X that hold pure numerical values like 123,456 etc.

I can not using regexp_like because I am using 10g.

Answer 1

Here are two options: one uses regular expressions, another translate function.

SQL> with test as
  2    (select 'a1b2c3' col from dual union all
  3     select 'abc'        from dual union all
  4     select '1ab'        from dual union all
  5     select '123'        from dual union all
  6     select '156-346'    from dual
  7    )
  8  select col,
  9    regexp_replace(col, '[^[:digit:]]') result,
 10    --
 11    translate(col, '0'|| translate(col, '$0123456789', '$'), '0') result2
 12  from test;

COL     RESULT  RESULT2
------- ------- -------
a1b2c3  123     123
abc
1ab     1       1
123     123     123
156-346 156346  156346

SQL>