Spelling Numbers

Wouldn't be nice to be able to display numbers in their spelling textual format? There are a lot of applications which require such a feature: billing, bookkeeping systems etc. Well, if we remember right there is a special suffix for specifying the mask to convert a date using the TO_CHAR function. That special suffix is SP. However, being designed to work with date types it's not very obvious how it can be used for plain numbers. Well, even it's not very often used we can convert a number into a date representation using the Julian format and then spell it using the SP prefix. For example:

SQL> select to_char(to_date(34, 'j'), 'jspth') spell from dual;

SPELL
-------------
thirty-fourth

SQL> select to_char(to_date(1023, 'j'), 'jspth') spell from dual;

SPELL
-------------------------
one thousand twenty-third

Lovely! Nevertheless, there are some limitations here, and some of them are quite ugly therefore they should be carefully analyzed before deciding to use this approach into a production system. The following are some of the problems you might encounter:

1. it's not suitable for multi-language applications. The SP prefix always speaks in English, despite of the current NLS settings.
2. It cannot be used to display large numbers. In fact, the maximum number which can be spelled using this approach is exactly 5373484. It's quite small for a bookkeeping system, right?
3. How about negative numbers or zero? Forget it! It will not work!