Alphabetic Dollar Amounts for Check Applications

Here's a trick from Jayne Albino on how to turn a numeric value, such as 54334, into an alphabetic value, such as FIFTY-FOUR THOUSAND THREE HUNDRED THIRTY-FOUR.  To do this, use the SP date format to spell out the number after converting the number to a date (using julian conversion):

select to_char(to_date(54334,'J'),'JSP') from dual;  -- For upper-case letters
select to_char(to_date(54334,'J'),'JsP') from dual;  -- For mixed-case letters
select to_char(to_date(54334,'J'),'jSP') from dual;  -- For lower-case letters

The minimum julian number allowed is 1, and, the maximum julian number allowed is 5373484, so, if you want to make a check out for a larger amount, just send it to me!  Also, this only works for integer amounts.  If you want dollars and cents, you'll have to split up the amount into it's integer and decimal parts, and handle the case of a zero amount, such as in:

select decode(trunc(54334.69),0,'ZERO',to_char(to_date(trunc(54334.69),'J'),'JSP'))
     || ' DOLLARS AND ' || decode(trunc(mod(54334.69,1)*100),0,'ZERO',
     to_char(to_date(trunc(mod(54334.69,1)*100),'J'),'JSP')) || ' CENTS'
     from dual;

Or, to make it general, using a variable amount (you'll have to do an "undefine amount" afterwards if you want to try out other values):

select decode(trunc(&&amount),0,'ZERO',to_char(to_date(trunc(&&amount),'J'),'JSP'))
     || ' DOLLARS AND ' || decode(trunc(mod(&&amount,1)*100),0,'ZERO',
     to_char(to_date(trunc(mod(&&amount,1)*100),'J'),'JSP')) || ' CENTS'
     from dual;

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.