DBA Tips Archive for Oracle

  


Convert Numbers to Words

by Brian Membrey, The PaperBag Software Company Pty Ltd

I guess a mundane exercise that most programmers cop at some stage is having to convert a number (123) into a text equivalent (ONE HUNDRED AND TWENTY THREE) - for cheques, group certificates, etc. Probably the code involved a loop stripping out the numerals and then applying a value according to the relative position within the overall value.

Although it winds a strange path via date functions, SQL*Plus actually provides a mechanism for automating much of this process. Executing the following :

    SELECT TO_CHAR ( TO_DATE ( TO_CHAR ( 103465, '99999999999') , 'J'),  'JSP') FROM dual;

returns a value of ONE HUNDRED THREE THOUSAND FOUR HUNDRED SIXTY-FIVE

If we break the statement into each component function, then what happens is :

SP can be used in a number of situations. For example, if SYSDATE is 26-AUG-98, then :

    SELECT TO_CHAR ( SYSDATE, 'DdSp') FROM dual;    -- spells the day as Twenty-Six, 
and
    SELECT TO_CHAR ( SYSDATE, 'DDSPTH') FROM dual;  --returns TWENTY-SIXTH

Some simple manipulations can be included with the base conversion to cover floating numbers or currencies (email brianm@lt.com.au for source), eg. 103465.27 becomes ONE HUNDRED AND THREE THOUSAND FOUR HUNDRED AND SIXTY-FIVE DOLLARS AND TWENTY-SEVEN CENTS.

One covenant however : if in your mad appreciation of this trivia you want to send me a cheque for more than $5,373,484.00, then you'll have to write it manually, or send more than one cheque!

SQL*Plus restricts Julian days to between 1 and 5373484, which won't be a problem for most applications, but should be borne in mind before using the technique in anger.

5373484 represents 31-Dec-9999, so this may be Oracle's way of introducing us to a Year 10K problem!


Copyright (c) 1998-2017 Jeffrey M. Hunter. All rights reserved.

All articles, scripts and material located at the Internet address of http://www.idevelopment.info is the copyright of Jeffrey M. Hunter and is protected under copyright laws of the United States. This document may not be hosted on any other site without my express, prior, written permission. Application to host any of the material elsewhere can be made by contacting me at jhunter@idevelopment.info.

I have made every effort and taken great care in making sure that the material included on my web site is technically accurate, but I disclaim any and all responsibility for any loss, damage or destruction of data or any other property which may arise from relying on it. I will in no case be liable for any monetary damages arising from such loss, damage or destruction.

Last modified on
Saturday, 23-Nov-2002 00:00:00 EST
Page Count: 23988