Convert number to fraction

In Oracle PL/SQL, working with numbers often requires precise formatting, conversions, and manipulation. Converting a decimal number into a fraction is not a built-in function, but it can be achieved using mathematical techniques. This article will walk through the process of converting numbers to fractions using PL/SQL, explaining the logic and providing practical examples.

Understanding the Concept of Fractions

A fraction consists of a numerator and a denominator, representing a rational number. For example, 0.75 can be represented as 3/4. The key to converting a decimal to a fraction is to determine the greatest common divisor (GCD) of the numerator and denominator, then reduce the fraction to its simplest form.

Steps to Convert a Decimal to a Fraction in PL/SQL

Extract the integer and decimal parts of the number.

Convert the decimal part into a fraction by multiplying by an appropriate power of 10.

Compute the greatest common divisor (GCD) to simplify the fraction.

Output the fraction in a readable format.

Implementing the Conversion in PL/SQL

Below is a PL/SQL function that converts a decimal number to a simplified fraction:

CREATE OR REPLACE FUNCTION 
number_to_fraction(p_number IN NUMBER) 
RETURN VARCHAR2 IS
    v_numerator   NUMBER;
    v_denominator NUMBER := 1;
    v_gcd         NUMBER;
    v_whole_part  NUMBER;
    v_fraction    VARCHAR2(50);

    -- Function to calculate GCD using Euclidean Algorithm
    FUNCTION gcd(a NUMBER, b NUMBER) RETURN NUMBER IS
        temp NUMBER;
    BEGIN
        WHILE b != 0 LOOP
            temp := b;
            b := MOD(a, b);
            a := temp;
        END LOOP;
        RETURN a;
    END gcd;

BEGIN
    -- Separate integer and decimal parts
    v_whole_part := TRUNC(p_number);
    v_numerator := ROUND((p_number - v_whole_part) * POWER(10, 6)); -- Convert decimal to integer
    v_denominator := POWER(10, 6);
    
    -- Compute GCD to simplify fraction
    v_gcd := gcd(v_numerator, v_denominator);
    v_numerator := v_numerator / v_gcd;
    v_denominator := v_denominator / v_gcd;
    
    -- Construct the fraction representation
    IF v_numerator = 0 THEN
        RETURN TO_CHAR(v_whole_part);
    ELSIF v_whole_part = 0 THEN
        v_fraction := TO_CHAR(v_numerator) || '/' || TO_CHAR(v_denominator);
    ELSE
        v_fraction := TO_CHAR(v_whole_part) || ' ' || TO_CHAR(v_numerator) || '/' || TO_CHAR(v_denominator);
    END IF;
    
    RETURN v_fraction;
END number_to_fraction;
/

Explanation of the Code

Extract Integer and Decimal Parts:

The TRUNC() function is used to extract the integer part.

The decimal part is multiplied by 10^6 to convert it into an integer.

Compute GCD:

The Euclidean algorithm is used to compute the greatest common divisor (GCD) of the numerator and denominator.

Reduce the Fraction:

The numerator and denominator are divided by their GCD to get the simplest fraction form.

Format the Output:

If there is no fractional part, only the integer is returned.

If there is a fractional part but no integer part, the fraction is returned.

If both parts exist, a mixed fraction is returned.

Testing the Function

You can test the function with different numbers:

SELECT number_to_fraction(0.75) FROM dual; 
-- Output: '3/4'

SELECT number_to_fraction(2.5) FROM dual;  
-- Output: '2 1/2'

SELECT number_to_fraction(0.3333) FROM dual; 
-- Output: '3333/10000' (simplified if possible)

Handling Special Cases

Whole Numbers:

If the input is a whole number (e.g., 4), the function returns “4”.

Recurring Decimals:

This implementation approximates recurring decimals (e.g., 1/3 as 3333/10000).

Negative Numbers:

The function can be modified to handle negative numbers correctly.

Conclusion

While Oracle PL/SQL does not have a built-in function for converting numbers to fractions, it can be implemented using mathematical logic. The key steps involve extracting integer and decimal parts, computing the GCD, and formatting the result properly. This function provides a practical way to convert and display fractions in a simplified form within Oracle databases.