Tuesday 3 August 2010

Oracle Function to Convert Numbers to Words

This is a basic function to covert integers to words. Pass the number into the function and it will return it spelt out, in your own language.

OK there are limitations to this, only positive integers less than 5373485, but if this fits your requirements then here you go. Should it hit a limitation it just returns the number back.

The Function:

FUNCTION simpleIntegerToWords
( p_number IN NUMBER ) RETURN VARCHAR2 IS
BEGIN
RETURN(TO_CHAR(TO_DATE(p_number,'j'),'jsp'));
EXCEPTION
WHEN OTHERS THEN
RETURN(p_number); --// For numbers > 5373484
END;

Run:

BEGIN

DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(1560));
DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(5373484));
DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(5373485));
DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(53.73));
DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(-53));
DBMS_OUTPUT.PUT_LINE(simpleIntegerToWords(2007));

END;
/


The result:

one thousand five hundred sixty
five million three hundred seventy-three thousand four hundred eighty-four
5373485
53.73
-53
two thousand seven

Spelt.

No comments: