How can I use Julian date to convert numbers to words?
Luckily, the TO_CHAR function can assist you. For instance, assume that I have the number '123' and I want to convert it to a word. Then a SQL statement such as the following will do the trick:
ORA9I SQL> select to_char(to_date('01-01-0123','MM-DD-YYYY'),'YEAR') from dual; TO_CHAR(TO_DATE( ---------------- ONE TWENTY-THREEIn this case, I put the number '123' in the four digit year. I then used the YEAR format mask to spell out the year in words. Here are a few more examples:
ORA9I SQL> select to_char(to_date('01-01-0456','MM-DD-YYYY'),'YEAR') from dual; TO_CHAR(TO_DAT -------------- FOUR FIFTY-SIX ORA9I SQL> select to_char(to_date('01-01-1234','MM-DD-YYYY'),'YEAR') from dual; TO_CHAR(TO_DATE('0 ------------------ TWELVE THIRTY-FOUR ORA9I SQL> select to_char(to_date('01-01-4567','MM-DD-YYYY'),'YEAR') from dual; TO_CHAR(TO_DATE('01-01 ---------------------- FORTY-FIVE SIXTY-SEVENAs you can see, the numbers are not spelled out exactly as you want. If you want '4567' to be spelled out as FOUR THOUSAND FIVE HUNDRED SIXTY SEVEN, then the example gets more complicated. And you'll have to write a bit of code to get the answer as you want. Luckily, Tom Kyte has already answered this question on his AskTom web site. The URL for his solution is: http://asktom.oracle.com/pls/ask/f?p=4950:8:249493602386220015::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:1407603857650,.
For More Information
- Dozens more answers to tough Oracle questions from Brian Peasland are available.
- The Best Oracle Web Links: tips, tutorials, scripts, and more.
- Have an Oracle or SQL 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 and SQL questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
Dig Deeper on Oracle database design and architecture
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.