Skip to Main Content
  • Questions
  • Convert a decimal to it's fraction representation

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Manish.

Asked: August 20, 2007 - 10:04 am UTC

Last updated: March 29, 2023 - 1:14 pm UTC

Version: 10.2.0.3

Viewed 10K+ times! This question is

You Asked

Hello,

This is math/algebra question, but I was interested to see if there was a sql based solution as I am pleasantly surprised by the depth and breath of solutions on this site.

At our company, we print labels, brochures etc. For each print job, the item's length and width are specified as number with upto 4 decimals.

For reporting purposes, it's necessary to show (for example) the width 2.875
as 2 7/8.

So, I want to essentially convert a number to a fraction representation. Any insight on how to solve will be greatly appreciated.

Thank You,

Manish

and Tom said...

I will not be surprised when my brute force implementation is surpassed by the elegant solutions I'm pretty sure will be posted as follow ups to this :)

ops$tkyte%ORA10GR2> create or replace function gcd(a number, b number)
  2  return number
  3  as
  4  begin
  5          /* borrowed from http://oraclesponge.wordpress.com/2006/06/13/summary-data-simulation-and-lcm-and-gcd-functions-in-plsql/ */
  6     if b = 0 then
  7        return a;
  8     else
  9        return gcd(b,mod(a,b));
 10     end if;
 11  end;
 12  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function to_fraction( p_number in number )
  2  return varchar2
  3  as
  4          l_gcd number;
  5          l_int integer;
  6          l_fract_str varchar2(30);
  7          l_numerator number;
  8          l_denominator number;
  9  begin
 10          l_int := trunc(p_number);
 11          l_fract_str := rtrim( to_char( p_number - l_int, 'fmv999999999' ), '0' );
 12
 13          l_numerator := to_number( l_fract_str );
 14          l_denominator := to_number( power(10,length(l_fract_str)) );
 15
 16          l_gcd := gcd( l_numerator, l_denominator );
 17
 18          return to_char( l_int ) || ' ' || l_numerator / l_gcd || '/' || l_denominator / l_gcd;
 19  end;
 20  /

Function created.

ops$tkyte%ORA10GR2> select x, to_fraction( x )
  2    from (select 1 + rownum/8 x from dual connect by level < 8 );

         X
----------
TO_FRACTION(X)
-------------------------------------------------------------------------------
     1.125
1 1/8

      1.25
1 1/4

     1.375
1 3/8

       1.5
1 1/2

     1.625
1 5/8

      1.75
1 3/4

     1.875
1 7/8


7 rows selected.

Rating

  (15 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

that IS a brute ...

Gabe, August 21, 2007 - 4:46 pm UTC

Your gcd is an infinite loop if b is null, since mod(anything, null) is null ...
Which is what happens when you pass an integer to to_fraction.

So, don't run that in production.
gabe@XE> select num, representation from
  2  (
  3  with s as
  4       (
  5         select 0     num from dual union all
  6         select 0.0   num from dual union all
  7         select 1.25  num from dual union all
  8         select 1.375 num from dual union all
  9         select 1.625 num from dual union all
 10         select 2.875 num from dual union all
 11         select 2.475 num from dual union all
 12         select 0.001 num from dual union all
 13         select 5.000 num from dual union all
 14         select 9.999 num from dual union all
 15         select 12345 num from dual
 16       )
 17      ,t as ( select level l from dual connect by level <= 1000)
 18      ,d as ( select s.num,min(t.l) l
 19              from   s, t
 20              where  s.num * t.l = floor(s.num * t.l)
 21              group by s.num
 22            )
 23  select d.num
 24        ,floor(d.num)
 25        ,d.l * (d.num - floor(d.num))
 26        ,d.l
 27        ,to_char(floor(d.num))||
 28               case d.l * (d.num - floor(d.num))
 29                 when 0 then null
 30                 else ' '||to_char(d.l * (d.num - floor(d.num))) || '/' || to_char(d.l)
 31               end representation
 32  from   d
 33  )
 34  order by 1
 35  ;

       NUM REPRESENTATION
---------- --------------------
         0 0
      .001 0 1/1000
      1.25 1 1/4
     1.375 1 3/8
     1.625 1 5/8
     2.475 2 19/40
     2.875 2 7/8
         5 5
     9.999 9 999/1000
     12345 12345

10 rows selected.

Elapsed: 00:00:00.02
gabe@XE>

Thanks Gabe

Ravi, August 22, 2007 - 6:02 am UTC

Gabe,
Its really a very neat solution.This forum is turning a double learning treat for me.

Using GCD

Rod West, August 22, 2007 - 8:34 am UTC

You can still use the GCD function if you want and this avoids the need for a connect by level.

SQL> create or replace function gcd(a number, b number)
  2  return number
  3  as
  4  begin
  5  if nvl(b,0) = 0 then return a;
  6  else return gcd(b,mod(a,b));
  7  end if;
  8  end;
  9  /

Function created.

SQL> 
SQL> select n, floor(n)||' '||nullif(x*p/gcd(x*p, p)||'/'||p/gcd(x*p, p), '0/1') representation
  2  from (
  3  select n, n-floor(n) x, power(10, length(to_char(n-floor(n)))-1) p
  4  from (
  5  select 0 n from dual union all
  6  select 0.001   n from dual union all
  7  select 1.25  n from dual union all
  8  select 1.375 n from dual union all
  9  select 1.625 n from dual union all
 10  select 2.875 n from dual union all
 11  select 2.475 n from dual union all
 12  select 0.001 n from dual union all
 13  select 5.000 n from dual union all
 14  select 9.999 n from dual union all
 15  select 12345 n from dual
 16  ))
 17  /

         N REPRESENTATION
---------- --------------------
         0 0
      .001 0 1/1000
      1.25 1 1/4
     1.375 1 3/8
     1.625 1 5/8
     2.875 2 7/8
     2.475 2 19/40
      .001 0 1/1000
         5 5
     9.999 9 999/1000
     12345 12345

11 rows selected.

SQL> 

Fix for negative numbers

Rod West, August 22, 2007 - 8:48 am UTC

Fixed for negative numbers:

SQL> select n, trunc(n)||' '||nullif(x*p/gcd(x*p, p)||'/'||p/gcd(x*p, p), '0/1') representation
  2  from (
  3  select n, abs(n-trunc(n)) x, power(10, length(to_char(n-trunc(n)))-1) p
  4  from (
  5  select -1.250  n from dual 
  6  ))
  7  /

         N REPRESENTATION
---------- --------------------
     -1.25 -1 1/4

SQL> 

exponential computation

Duke Ganote, August 22, 2007 - 9:50 am UTC

I modestly generalized Gabe's implementation (below), but the computational requirement grows exponentially (see the POWER function below) with the number of right-side decimals.

SELECT cast(num as char(10))
|| ' = '||representation
as fraction_representation
FROM
(
with sample_data as
(
select 0.0 num from dual union all
select 1.25 num from dual union all
select 0.001 num from dual union all
select 5.000 num from dual union all
select 3.14159 num from dual union all
select 12345 num from dual
)
,count_max_decimals as
( select max(length(num-trunc(num))) max_decimals
from sample_data
)
,all_possible_denominators as
( select level l
from count_max_decimals
connect by level <= power(10,max_decimals)
)
,lowest_denominator as
( select s.num,min(t.l) l
from sample_data s,
all_possible_denominators t
where s.num * t.l = floor(s.num * t.l)
group by s.num
)
SELECT d.num
,floor(d.num)
,d.l * (d.num - floor(d.num))
,d.l
,CASE floor(d.num)
WHEN 0 THEN CASE d.l * (d.num - floor(d.num))
WHEN 0 THEN 0
END
ELSE floor(d.num)
END ||' '||
CASE d.l * (d.num - floor(d.num))
WHEN 0 THEN null
ELSE to_char(d.l * (d.num - floor(d.num)))
|| '/' || to_char(d.l)
END representation
FROM lowest_denominator d
)
order by 1
/
FRACTION_REPRESENTATION
----------------------------

.001 = 1/1000
0 = 0
1.25 = 1 1/4
12345 = 12345
3.14159 = 3 14159/100000
5 = 5

More than useful

Manish, August 22, 2007 - 10:18 am UTC

Thank you Tom and other community members. Just when I thought something cannot be done in SQL, you find a way. I must say, I fail to realize how you formulate a sql solution to a seemingly procedural solution.

Thanks,

Manish

SQL vs PL/SQL

Duke Ganote, August 22, 2007 - 10:48 am UTC

"how [do] you formulate a sql solution to a seemingly procedural solution"?
It's all about thinking in 'sets' rather than row-by-row. See
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5500485045890#tom47352208686203
among others...

An even more general solution

Craig, August 22, 2007 - 11:46 am UTC

Here is a general case that handles terminating decimals, repeating decimals, improper fractions, negative fractions, whole numbers, and any combination of the preceding. I *think* I've tested just about every case. I'm sure this can be done more elegantly as well. This is infinitely more fun that what I should actually be working on.


DECLARE

p_decimal NUMBER := -7;
-- p_decimal NUMBER := 0.25;
-- p_decimal NUMBER := 3/4;
-- p_decimal NUMBER := 1.25;
-- p_decimal NUMBER := -5/3;
-- p_decimal NUMBER := -2/3;
-- p_decimal NUMBER := 1/3;
-- p_decimal NUMBER := 2/15;
-- p_decimal NUMBER := 7/15;

a NUMBER;
a_power NUMBER := 0;
a_factor NUMBER := 0;
ax NUMBER;
p NUMBER := 0;
diff NUMBER;
diff_prev NUMBER := 9999999999999999999;

lv_unit NUMBER;
lv_unit_v VARCHAR2(25);
lv_numerator NUMBER;
lv_denominator NUMBER;
lv_fraction VARCHAR2(25);

BEGIN
a := abs(p_decimal);
dbms_output.put_line(a);


LOOP
p := p + 1;
a_factor := power(10, a_power) * a;

ax := (power(10, p) * a);

diff := ax - a_factor - trunc(ax);

EXIT WHEN diff = floor(diff);

IF diff >= diff_prev THEN
a_power := a_power + 1;
a_factor := power(10, a_power) * a;
p := p - a_power;
diff_prev := 9999999999999999999;
ELSE
diff_prev := diff;
END IF;

END LOOP;

lv_numerator := ax - a_factor;
lv_denominator := (power(10,p) - power(10,a_power));

lv_fraction := (lv_numerator * sign(p_decimal)) || '/' || lv_denominator;
dbms_output.put_line('Unreduced: ' || lv_fraction);


-- Simplify the fraction
FOR i IN REVERSE 2..trunc(lv_numerator) LOOP
IF (lv_numerator / i) = trunc(lv_numerator / i)
AND (lv_denominator / i) = trunc(lv_denominator / i) THEN
lv_numerator := lv_numerator / i;
lv_denominator := lv_denominator / i;
END IF;
END LOOP;

lv_fraction := (lv_numerator * sign(p_decimal)) || '/' || lv_denominator;
dbms_output.put_line('Reduced, potentially improper: ' || lv_fraction);


-- elminate the improper fraction
IF lv_numerator > lv_denominator
AND lv_denominator != 1 THEN
lv_unit := MOD(lv_numerator, lv_denominator);
lv_numerator := lv_numerator - lv_denominator;
lv_unit_v := lv_unit * sign(p_decimal) || ' ';
ELSE
lv_unit_v := NULL;
lv_numerator := lv_numerator * sign(p_decimal);
END IF;

CASE WHEN lv_denominator = 1 THEN
lv_fraction := lv_unit_v || lv_numerator;
ELSE
lv_fraction := lv_unit_v || lv_numerator || '/' || lv_denominator;
END CASE;

dbms_output.put_line('Reduced and proper: ' || lv_fraction);

END;
/

Tom Kyte
August 22, 2007 - 2:24 pm UTC

... This is infinitely more fun that what I should
actually be working on.....


laughed out loud at that :) very nice

Fixed for recurring decimals

Rod West, August 23, 2007 - 8:15 am UTC

Tom, I haven't had so much fun for ages!

This version I think will work for all recurring decimals upto 9 places, as well as negatives and integers.

SQL> CREATE OR REPLACE FUNCTION gcd(a NUMBER, b NUMBER)
  2  RETURN NUMBER
  3  AS
  4  BEGIN
  5  IF NVL(b,0) = 0 THEN RETURN a;
  6  ELSE RETURN gcd(b,MOD(a,b));
  7  END IF;
  8  END;
  9  /

Function created.

SQL> SELECT n, DECODE(SIGN(n), 0, '0', -1, '-'||i||f, i||f) rep
  2  FROM (
  3  SELECT  n, NULLIF(TRUNC(ABS(n))||' ','0 ') i, NULLIF(x*p/g||'/'||p/g, '0/1') f
  4  FROM (
  5  SELECT n, x, GREATEST(g1,g2, g3) g, DECODE(GREATEST(g1,g2,g3), g1, p1, g2, p2, p3) p
  6  FROM (
  7  SELECT n, x,  gcd(x*p8, p8) g1, gcd(x*(p8-1), p8-1) g2, gcd(x*(p7-1), p7-1) g3,  p8 p1, p8-1  p2, p7-1 p3
  8  FROM (
  9  SELECT n, ABS(n-TRUNC(n)) x, POWER(10, 36) p8, POWER(10, 35) p7
 10  FROM (
 11  SELECT -1/1023  n FROM dual UNION ALL
 12  SELECT 3/7  n FROM dual UNION ALL
 13  SELECT 7/15  n FROM dual UNION ALL
 14  SELECT -1.75  n FROM dual UNION ALL
 15  SELECT 17/1111111  n FROM dual UNION ALL
 16  SELECT 12345678/111111111  n FROM dual UNION ALL
 17  SELECT 123456789/1111111111  n FROM dual UNION ALL
 18  SELECT -123  n FROM dual UNION ALL
 19  SELECT 0  n FROM dual
 20  )))))
 21  /

                                       N REP
---------------------------------------- ----------------------------------------------------------------------------------------------------
-.00097751710654936461388074291300097752 -1/1023
.428571428571428571428571428571428571429 3/7
.466666666666666666666666666666666666667 7/15
                                   -1.75 -1 3/4
    .00001530000153000015300001530000153 17/1111111
.111111102111111102111111102111111102111 1371742/12345679
 .11111111011111111101111111110111111111 1100110001100110010011001100011001099/9900990099009900990099009900990099000
                                    -123 -123
                                       0 0

9 rows selected.          


Pride comes before a fall

Craig, August 23, 2007 - 8:53 am UTC

My wife was a math major and as I was bragging to her about how clever I was last night. That is, until she pointed out that 5/3 is not equivalent to 2 2/3, as my script so claimed. Please accept my apologies, along with this revision.

-- elminate the improper fraction
IF lv_numerator > lv_denominator
AND lv_denominator != 1 THEN
lv_unit := (lv_numerator - mod(lv_numerator, lv_denominator)) / lv_denominator;
lv_numerator := mod(lv_numerator, lv_denominator);
lv_unit_v := lv_unit * sign(p_decimal) || ' ';
ELSE
lv_unit_v := NULL;
lv_numerator := lv_numerator * sign(p_decimal);
END IF;

SQL just for fun ...

Gabe, August 23, 2007 - 11:24 am UTC

Duke,

I was expecting that to come up ... yes, that brutish SQL solution will not scale as the number of inputs or the number of decimals increases. Having said that, in your query power(10,max_decimals-1) would suffice.

For production use, a pl/sql solution based on gcd would likely be better ... deterministic functions, result_cache in 11g ... yes.

For fixed number of decimals, 4 as mentioned by the OP ... even a do-it-yourself cache would be an option: create an IOT with 9999 rows having the results pre-computed for everything between 0.0001 and 0.9999 and join to it. Actually one only needs to compute the results for everything between 0.0001 and 0.5000 ... the rest till 0.9999 would be derived.

gabe@XE> with s as
  2       (
  3         select 1 id,  0.499 num from dual union all
  4         select 2 id,1-0.499 num from dual union all
  5         select 3 id,  0.375 num from dual union all
  6         select 4 id,1-0.375 num from dual union all
  7         select 5 id,  0.265 num from dual union all
  8         select 6 id,1-0.265 num from dual
  9       )
 10      ,t as ( select level l from dual connect by level <= 1000)
 11      ,d as ( select s.id,s.num,min(t.l) l
 12              from   s, t
 13              where  s.num * t.l = floor(s.num * t.l)
 14              group by s.id, s.num
 15            )
 16  select d.num
 17        ,floor(d.num)                  integer_part
 18        ,d.l * (d.num - floor(d.num))  numerator
 19        ,d.l                           denominator
 20  from d
 21  order by id
 22  ;

       NUM INTEGER_PART  NUMERATOR DENOMINATOR
---------- ------------ ---------- -----------
      .499            0        499        1000
      .501            0        501        1000
      .375            0          3           8
      .625            0          5           8
      .265            0         53         200
      .735            0        147         200


Well, to avoid both recursive CPU and I/O, one might even consider a result_cache pl/sql function having relies_on such a table with pre-computed values.

Cheers.

Taming <i>Brute</i> SQL

Duke Ganote, August 24, 2007 - 10:28 am UTC

Gabe-- Exactly. The SQL was merely a starting point for reviewing the Fundamental Theorem of Arithematic, etc. "Infinitely more fun that what I should actually be working on" as Craig noted below. But one lacks infinite time for chasing all the rabbits in the field of dreams!
-- Duke

Fundamental Theorems, etc

Duke Ganote, August 24, 2007 - 10:42 am UTC

I might as well confess: I suffer from "chemical illiteracy"
http://www.cut-the-knot.org/manifesto/index.shtml

Very interesting discussion..

Venkat, September 25, 2007 - 2:22 pm UTC

I just saw this after returning from a long vacation and I had to try this out..
I created a modifed gcd so we can evaluate 0.6667 as 2/3 and 0.3333 as 1/3, by adding a precision parameter to the function.

create or replace function gcd (a number, b number,
p_precision number default null,
orig_larger_num number default null) return number is
v_orig_larger_num number := greatest(nvl(orig_larger_num,-1),a,b);
v_precision_level number := p_precision;
begin
if a is null or b is null or (a = 0 and b = 0) then return 1; end if; -- debatable
if p_precision is null or p_precision <= 0 then
v_precision_level := 4; -- within 1/10000
end if;
if b is null or b = 0 or
(b/v_orig_larger_num <= power(10,-1*v_precision_level)
and greatest(a,b) <> v_orig_larger_num) then
return a;
else
return (gcd(b,mod(a,b),v_precision_level,v_orig_larger_num));
end if;
end;
/

select num, int_part, numer, denom, gcdval,
case when num = 0 then '0'
when int_part = 0 then ''
else to_char(int_part)||' '
end ||
case when numer = 0 then ''
else to_char(trunc(numer/gcdval))||'/'||to_char(trunc(denom/gcdval))
end fraction
from (
select num, mod(num,1), length(mod(num,1))-1 pwr, trunc(num) int_part,
mod(num,1)*power(10,length(mod(num,1))-1) numer,
power(10,length(mod(num,1))-1) denom,
gcd(power(10,length(mod(num,1))-1),
mod(num,1)*power(10,length(mod(num,1))-1)) gcdval
from (
select 0 num from dual union all
select 0.0 num from dual union all
select 1.25 num from dual union all
select 1.375 num from dual union all
select 1.625 num from dual union all
select 2.875 num from dual union all
select 2.475 num from dual union all
select 0.001 num from dual union all
select 5.000 num from dual union all
select 9.999 num from dual union all
select 12345 num from dual union all
select .265 num from dual union all
select 0.735 num from dual union all
select .3333 num from dual union all
select .6667 num from dual union all
select .6666 num from dual
))

NUM INT_PART NUMER DENOM GCDVAL FRACTION
0 0 0 1 1 0
0 0 0 1 1 0
1.25 1 25 100 25 1 1/4
1.375 1 375 1000 125 1 3/8
1.625 1 625 1000 125 1 5/8
2.875 2 875 1000 125 2 7/8
2.475 2 475 1000 25 2 19/40
0.001 0 1 1000 1 1/1000
5 5 0 1 1 5
9.999 9 999 1000 1 9 999/1000
12345 12345 0 1 1 12345
0.265 0 265 1000 5 53/200
0.735 0 735 1000 5 147/200
0.3333 0 3333 10000 3333 1/3 <----- using default precision
0.6667 0 6667 10000 3333 2/3 <-----
0.6666 0 6666 10000 2 3333/5000

--Venkat.


What about TO_CHAR()?

Stevevn Meyer, March 28, 2023 - 4:51 pm UTC

It sure would be nice if those clever people at Oracle could find a way to incorporate this in as a Format Model for TO_CHAR(numeric_value, format_string);
I mean, if MS Excel can do it...
Displaying Formulas
Line Numerator Denominator Ratio Fraction
1 =TRUNC(1+RAND()*200) =TRUNC(1+RAND()*200) =B2/C2 =D2
=A2+1 =TRUNC(1+RAND()*200) =TRUNC(1+RAND()*200) =B3/C3 =D3
=A3+1 =TRUNC(1+RAND()*200) =TRUNC(1+RAND()*200) =B4/C4 =D4
=A4+1 =TRUNC(1+RAND()*200) =TRUNC(1+RAND()*200) =B5/C5 =D5
Displaying (Possible) Results of Formulas
Line Numerator Denominator Ratio Fraction
1 137 78 1.7564 1 59/78
2 14 161 0.0870 2/23
3 85 93 0.9140 85/93
4 139 159 0.8742 7/8

Chris Saxon
March 29, 2023 - 1:14 pm UTC

What exactly are you proposing? How would it work?

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library