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 email@example.com 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 firstname.lastname@example.org.
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.