Re: converting a fraction to a decimal

Assuming you will always have the fraction in the form, x-y/z you can create a function like this and call it from SQL
You will have to make modifications to the function to handle exceptions if it is not always x-y/x format or if it is null

SQL> create or replace
  2 function fraction_to_decimal (pfraction IN varchar2)   3 return number
  4 is
  5 n number;
  6 begin
  7 execute immediate

  8       'select ' ||
  9       replace(pfraction, '-', '+') ||
 10       ' from dual'
 11       into n;

Function created.

SQL> select fraction_to_decimal('16-3/4')   2 from dual
  3 /

FRACTION_TO_DECIMAL('16-3/4')

                        16.75

SQL>
"Seth" <sgelberg_at_optonline.net> wrote in message news:f3159b28.0203191334.3ba1e744_at_posting.google.com...
> select O.ord_limit, to_number(decode(instr(O.ord_limit, '-'), 0,
> O.ord_limit, substr(O.ord_limit, 1, instr(O.ord_limit, '-') - 1))||
> (substr(O.ord_limit, instr(O.ord_limit, '-') + 1, instr(O.ord_limit,
> '/') - instr(O.ord_limit, '-') - 1))/
> (substr(O.ord_limit, instr(O.ord_limit, '/') + 1))) --,
> instr(O.ord_limit, '/') - instr(O.ord_limit, '-') - 1))
> from orders o;
>
>
> where orders table is key int , ord_limit char 10
>
> and the value to convert is (ord_limit) 16-3/4
>
> does anyone have an easier way to do this?
>
Received on Wed Mar 20 2002 - 02:20:41 CET