Amount in Words - Conversion in single SQL statement - Only in Oracle, Oracle

Amount in Words - Conversion in single SQL statement - Only in Oracle

--Oracle 11g Express Edition
--please drop objects you've created at the end of the script 
--or check for their existance before creating
--'\\' is a delimiter

--select banner as "oracle version" from v$version;

SELECT TO_CHAR(TO_DATE(1012345,'j'),'jsp') AS AmountInWords FROM dual

-- How this works?

-- 1. The query first converts the number value to the julian days (as per julian calendar),
-- which is the number of days since January 1, 4712 BC. This is done by using format 'j' in TO_DATE()

-- 2. Then 'jsp' used in outer TO_CHAR conversion function for spell out the returned julian day number

-- 3. This is useful in billing/invoice module to convert the amount in words as we write in cheque

-- 4. The constant 1012345 can be replaced by any number column or expressions

-- Note: This wont work for decimals like 12345.67, then it should be truncated into two parts or 
-- to be used as a two different query for integer and fractional part.  
-- Also this can be tweaked with CREATE FUNCTION or PL/SQL block if necessary.


run \| edit \| history \| help	0