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.
to post a comment.
[Oracle] "Merging" groups column-wise and picking first non-null (ordered by another column)
temporal overlap in oracle
Character inception test
Oracle Sandbox: Dog Data