Home » SQL & PL/SQL » SQL & PL/SQL » Format conversion +999,999,999.99
Format conversion +999,999,999.99 [message #36559] Tue, 04 December 2001 12:26 Go to next message
kriser
Messages: 11
Registered: October 2001
Junior Member
All,
I have a currency records I am importing using UTL_FILE. They are defined on the input file in the following format: +999,999,999.99. Obviously when I try to read these values into a NUMERIC field I get a numeric conversion exception.

Does anyone have or know of a function that handles stripping all non-numeric values (EXCEPT FOR THE DECIMAL) out of a given field or can make the system accept this value as numeric?

Thank you for any assistance you can provide,
K R

----------------------------------------------------------------------
Re: Format conversion +999,999,999.99 [message #36560 is a reply to message #36559] Tue, 04 December 2001 12:39 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
select 
to_number('-999,999,999.99', 's999,999,999.99')
 from dual
union
select 
to_number('+0,123.45', 's999,999,999.99')
 from dual
 union
select 
to_number('+999,999,999.99', 's999,999,999.99')
 from dual;
 
-999999999.99
       123.45
 999999999.99

s is for sign


----------------------------------------------------------------------
Re: Format conversion +999,999,999.99 [message #36562 is a reply to message #36560] Tue, 04 December 2001 14:02 Go to previous message
kriser
Messages: 11
Registered: October 2001
Junior Member
Andrew, thank you for the Help!
This is exactly what I was looking for!!

----------------------------------------------------------------------
Previous Topic: Using Triggers to call procedures
Next Topic: Using instr and substr
Goto Forum:
  


Current Time: Tue Mar 31 11:29:34 CDT 2020