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

Hi Jurij,

tried your buggy example with our configuration unde SQL*Plus and it worked.

Configuration on NT4:

Oracle7 Server Release 7.3.3.4.0 - Production Release With the distributed option
PL/SQL Release 2.3.3.4.0 - Production

Jurij Modic wrote:
>
> 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
 

-- 
Regards

Matthias Gresz    :-)