The Site With the Lamp

April 17, 2007

Factorials as we all know can be defined as

Even for very small values of n, these numbers can become mind boggling. They cannot easily be calculated using multiplication of long integers. For example 20! is equal to 2432902008176640000 and it keeps growing and growing.

Obviously these numbers cannot easily fit into ints,floats, doubles,reals, (call it what you wish ) supported by your favourite programming language. SQL on the other hand has a data type known as NUMERIC that can store a number with upto 65 digits. It does that by saving the number as a string.

In mysql the NUMERIC data type is called DECIMAL and they don't exactly store the value as a string but it can be used to calculate factorials for integers upto 50 but the 51st factorial has more than 65 digits so it will not fit in. Here is simple stored procedure that will help you calculate these numbers.

    /** FACTORIALS **/
CREATE TABLE factorials(inumber int primary key, dvalue decimal(65,0));

DELIMITER //
DROP PROCEDURE find_factorials//

CREATE PROCEDURE find_factorials(P INT)
BEGIN
    DECLARE i INT;
    DECLARE y DECIMAL;
    DECLARE result DECIMAL;
    
    TRUNCATE factorials;
    INSERT INTO factorials values(0,1);
    INSERT INTO factorials values(1,1);
    INSERT INTO factorials values(2,2);

    SET i=2;
    WHILE i < P DO
        SELECT dvalue INTO y FROM factorials WHERE inumber = i;
        SELECT CAST(y * (i+1) AS DECIMAL ) INTO result ;
        INSERT INTO factorials values(i+1,result);
        SET i = i+1;
    END WHILE;
END//
DELIMITER ;

If you look closely at the numbers generated you find that the last 12 digits of 50! are zeros. So we can stretch our calculates a bit further if we chop of them but we are still going to run into the limit at 51. You might say we can divide by something larger that 10^12 and carry on with our calculations. Unfortunately that doesn't work out because the presence of the decimal point leads to a loss of accuracy.