Re: Numeric to Character Conversion (e.g. 10 to TEN)

On 19 Jan 1998 15:16:54 GMT, oracle_at_tchp2.tcamuk.stratus.com (Neil Chandler) wrote:

>I don't suppose that anyone has come up against a function to
>decode numeric numbers into verbal numbers.
>
>e.g. 10 converts to Ten
> 2105 converts to Two thousand one hundred and five
>
>I have a couple of programmers insisting that they have come across such a
>function before.

There was (stil is?) a script somewhere at Oracle Worldwide Support Web page that deal with this kind of conversion. It take the advantage of converting number strings to Julian date formats (using format mask 'J') and then back to splled strings using format mask 'JSP' (Julian SPelled), which spells numbers.

Examples:

SQL> SELECT TO_CHAR(TO_DATE('10','J'),'JSP') spell FROM dual;

SPE

---
TEN

SQL> SELECT TO_CHAR(TO_DATE('2105','J'),'JSP') spell FROM dual;

SPELL
-----------------------------
TWO THOUSAND ONE HUNDRED FIVE

SQL> SELECT TO_CHAR(TO_DATE('101211','J'),'JSP') spell FROM dual;

SPELL
-------------------------------------------
ONE HUNDRED ONE THOUSAND TWO HUNDRED ELEVEN


Since jou can have numbers representing julian dates from 1 to
something above 5,300,000 that is aproximate interval you can use this
method for spelling numbers. Of course, with use of date arithmetic
and DECODE you can shift this range to cover also negative numbers.
However, I found out the 'JSP' conversion to have bug - it spells some
particular number paterens wrong. For example, a slightly modified
last example from above gives wrong result:

SQL> SELECT TO_CHAR(TO_DATE('100211','J'),'JSP') spell FROM dual;

SPELL
-------------------------
ONE HUNDRED  THOUSAND TWO  (missing 'hundred eleven' at the end !!!!)



>regards

>

>Neil Chandler



Regards,
============================================================
Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================
The above opinions are mine and do not represent any official 
standpoints of my employer