Retrive Only Numeric Fields from a String

[himridul]

Hi,

I have a input string . I need only the number from this field using REPLACE or TRANSLATE or any function in the SELECT statement.

eg,

the input string is :
' 1 :: 2 3 Adfre 34 * & )( -= bd '

It should return only the numeric field
12334


I tried with
SELECT TRANSLATE(string,TRANSLATE(string,'0123456789',' '),'') from dual;

-- But it's not working properly.

Any help plz .......

 

[gunjan14]

Hi,

Try this code,

select translate('your string', 
                 translate('your string','0123456789', ' ')
                 ' ')
from   dual;

Example with your string

select translate('  1 ::  2 3 Adfre 34 * & )( -= bd ',
            translate('  1 ::  2 3 Adfre 34 * & )( -= bd ', 
                      '0123456789', 
                      ' '
                      ),
                 ' '
                 )
from   dual;

Tell if it works...

Gunjan

 

[BJCooperIT]

Here is code for a generic string conversion:

SQL> select
  2  (TRANSLATE(
  3  (TRANSLATE(
  4   upper('  1 ::  2 3 Adfre 34 * & )( -= bd '),
  5   ',./;''[]\=-`~!@#$%^&*()_+{}:"<>?ABCDEFGHIJKLMNOPQRSTUVWXYZ ',
  6   '                                                          ')),
  7   '1234567890 ','1234567890')) text
  8  from dual;

TEXT
-----
12334

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant/Custom Forms & PL/SQL - Oracle 8i & 9i - Windows 2000