Systems Engineering and RDBMS

Translating Number into Words

Posted by decipherinfosys on September 15, 2007

At times, we run into a situation where we have to spell out a number. For example, we may have to show amount in words i.e. $100 should be displayed as ‘One hundred dollars’ or 1st prize winner should be specifically displayed as ‘first prize winner’ (Ordinal numbers). In such cases, we will have to spell out the numbers.

Oracle provides simple way to do this. We can use SP and SPTH suffix in conjunction with TO_DATE and TO_CHAR function to spell out the numbers. We can take number and convert it using TO_DATE function with date format model ‘J’ (indicates Julian date) and then convert it to character string using TO_CHAR and SP or SPTH suffix. Let us see it with an example. Connect to SQL*Plus with proper credentials and run following query.

SELECT 1,to_char(to_date(1,’J’),’JSP’), to_char(to_date(1,’J’),’JSPTH’)
FROM DUAL
UNION ALL
SELECT 2,to_char(to_date(2,’J’),’JSP’), to_char(to_date(2,’J’),’JSPTH’)
FROM DUAL
UNION ALL
SELECT 123,to_char(to_date(123,’J’),’JSP’), to_char(to_date(123,’J’),’JSPTH’)
FROM DUAL
/

Here is the output of the query.

1 TO_CHAR(TO_DATE(1,’J’),’ TO_CHAR(TO_DATE(1,’J’),’
——— ———————— ————————
1 ONE                      FIRST
2 TWO                      SECOND
123 ONE HUNDRED TWENTY-THREE ONE HUNDRED TWENTY-THIRD

For working with dates, we can simply use DDTH suffix. Following is the example followed by an output.

SELECT TO_CHAR(sysdate, ‘DDth’)||’ ‘||TO_CHAR(sysdate, ‘Month’)||
‘, ‘||TO_CHAR(sysdate, ‘YYYY’) Current_Date
FROM DUAL
/

CURRENT_DATE
——————–
15TH September, 2007

As shown, this is very simple approach to spell out numbers correctly. One can write a function to pass in the number and return the spelled out corrected string.

Sorry, the comment form is closed at this time.

 
%d bloggers like this: