Problem solve Get help with specific problems with your technologies, process and projects.

Display a number in words

Here is how to display a number in words in Oracle.

I recently found a cool feature in Oracle -- displaying a number in words. It's available from 7.3.4.5.0 onward. It works for both small and large numbers. Here are a few examples:

SQL> select to_char(to_date(124,'J'), 'Jsp') from dual
Output: One Hundred Twenty-Four

SQL> select to_char(to_date(124,'J'), 'jsp') from dual
Output: one hundred twenty-four

SQL> select to_char(to_date(124,'J'), 'JSP') from dual
Output: ONE HUNDRED TWENTY-FOUR

SQL> select to_char(to_date(1234567,'J'), 'jsp') from dual
Output: one million two hundred thirty-four thousand five hundred sixty-seven

SQL> select to_char(to_date(5373333,'J'), 'jsp') from dual
Output: five million three hundred seventy-three thousand three hundred thirty-three

Reader Feedback

Roger R. writes: I ran into a limitation for certain dates: the Julian date function seems to miscount in that it thinks the day after the 1721057th day falls in the non-existent year zero so it has 365 days between 12/31/01 BC and 1/1/01 AD. So as unlikely as it is that you'll run into it, there are 366 numbers that can't be displayed in words, not to mention those numbers outside the limits of Julian dates.

SYSDATE
----------------------
AD 2002-07-22 10:36:24

BANNER
----------------------------------------------------------------
Oracle8i Enterprise Edition Release 8.1.7.2.0 - Production
PL/SQL Release 8.1.7.2.0 - Production
CORE 8.1.7.0.0 Production
TNS for HPUX: Version 8.1.7.2.0 - Production
NLSRTL Version 3.4.1.0.0 - Production

TO_CHAR(TO_DATE(1721057,'J'),'JSP')
---------------------------------------------------------
one million seven hundred twenty-one thousand fifty-seven

select to_char(to_date(1721058,'J'),'jsp') from dual
                       *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

select to_char(to_date(1721423,'J'),'jsp') from dual
                       *
ERROR at line 1:
ORA-01841: (full) year must be between -4713 and +9999, and not be 0

TO_CHAR(TO_DATE(1721424,'J'),'JSP')
----------------------------------------------------------------------
one million seven hundred twenty-one thousand four hundred twenty-four

Murali C. writes: This feature is very nice, but at the same time is there any way to display the number in different formats like

1) 1234567  =     1,234,567  [$]
2) 1234567  =     12,34,567  [Rs]
You need to pass the currency format to the query and it should split the number like above.

Paul B. writes: I think the readers should know that this feature of Oracle works for numbers between 1 and 5,373,484.

Radhakrishnan writes:

So long as the number is within the Julian date range, you can do the following to translate numbers to words:

select decode( sign( &num ), 
 -1, 'Minus ', 0, 'Zero', NULL ) ||
 decode( sign( abs(&num) ), +1, to_char ( to_date( abs(&num),'J'),'Jsp') )
 from dual

For More Information

  • What do you think about this tip? E-mail the editor at tdichiara@techtarget.com with your feedback.
  • The Best Oracle Web Links: tips, tutorials, scripts, and more.
  • Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize--submit your tip today!
  • Ask your technical Oracle questions--or help out your peers by answering them--in our live discussion forums.
  • Check out our Ask the Experts feature: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.

Dig Deeper on Oracle and SQL

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close