Home » SQL & PL/SQL » SQL & PL/SQL » Char to Numeric Functionality
Char to Numeric Functionality [message #199151] Fri, 20 October 2006 10:46 Go to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Hi all,

select LPAD 
                      (  TO_NUMBER
                            (TRANSLATE
                                      (UPPER (NVL (trim(a), '0')),
                                       'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890 ',
                                       '0000000000000000000000000012345678900'
                                      )
                            )
                       * 100,
                       4,
                       '0'
                      )		from dual


I want to convert a character variable to a numberic variable. If its not numeric i want that to be 0000. My statement fails if I encounter a special character or something other than translate characters.
I am with 9i(no NANVL).

regards
Srivaths
Re: Char to Numeric Functionality [message #199186 is a reply to message #199151] Fri, 20 October 2006 20:48 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Does this help?

select translate(str, '0123456789' || str, '0123456789' || lpad('0', length(str), '0'))
from (
    select 'jqf0841-147`090`9hfq;lknfc]\qr9ur3' as str
    from dual
)


Ross Leishman
Re: Char to Numeric Functionality [message #199198 is a reply to message #199186] Sat, 21 October 2006 00:29 Go to previous messageGo to next message
srivaths
Messages: 60
Registered: June 2005
Location: France
Member

Hi Ross Leishman,

It worked!!! Thanks a Lot.

Juz curious to know if there is any built in function in oracle which can perform is_numeric() or is_char() functionality?

regards
Srivaths

Re: Char to Numeric Functionality [message #199226 is a reply to message #199198] Sat, 21 October 2006 19:20 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
You can browse all of the available functions in the manual.

Ross Leishman
Previous Topic: VARIABLE IN PLSQL IN WHERE CLAUSE
Next Topic: package
Goto Forum:
  


Current Time: Thu Dec 08 04:23:02 CST 2016

Total time taken to generate the page: 0.26309 seconds