Character to number conversion

Question

I have a column with for eg values: *.4176, *0.2734, $1.53, $122.00, D0.4645, D.2464, *0.3426, *.3426, $0.0/$2.50, -0.0/-2.50

I need to write a query to display only the numerical part in numerical format. I want to create a detail object for the main object in business object. I tried using translate but it has a lot of hardcoding and the slash is creating problem to convert it into TO_BINARY_DOUBLE. Is there anyother way to avoid hardcoding and still display numbers in numerical format?

Answer 1

Try this:

SELECT TRANSLATE('*4176', ' */''', ' ') FROM DUAL;

This is the case when you have the symbol * in the column, just to get the idea. Perform some more translates for the other cases as well.

Cheers

Answer 2

I require output in following format:
Cost Formula Cost Formula Code Cost Formula Value Cost calculation $520.80 $ 520.8
*1 * 1
-0.0/-$20 -0.0/-$20 +0.0/-$36 +0.0/-$36

select 
cost_formula,
case
when TRANSLATE(COST_FORMULA,'1234567890.-+ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#%^&*()~`_[]{};<>:"*$',' ')='/' 
then ''
else TRANSLATE(COST_FORMULA,'1234567890.-+',' ')
END code,
case
when TRANSLATE(COST_FORMULA,'1234567890.-+ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#%^&*()~`_[]{};<>:"*$',' ')='/' 
then '0'
else TO_BINARY_DOUBLE(RTRIM(LTRIM(TRANSLATE(UPPER(COST_FORMULA),'ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#%^&*()~`_[]{};<>:"*$','0'))))
END value,
case 
when TRANSLATE(COST_FORMULA,'1234567890.-+ABCDEFGHIJKLMNOPQRSTUVWXYZ!@#%^&*()~`_[]{};<>:"*$',' ')='/' 
then cost_formula
else ''
END Calculation
FROM RSE.RSE_SO_F_ORDERS;

I am using this query to get the required format. But the numerical part is throwing the following error: ORA-00932: inconsistent datatypes: expected CHAR got BINARY_DOUBLE 00932. 00000 - "inconsistent datatypes: expected %s got %s"

And i want the numerical part to be as a float.