how to display number value in words

问题:

Q. Display the number value in Words and output should look like this

SAL        In_Words
--------- -----------------------------------------------------
800       eight hundred
1600      one thousand six hundred
1250      one thousand two hundred fifty

And, I'm still didn't figure out, how this query is the solution for the above output.

select sal, to_char(to_date(sal,'j'),'Jsp') in_words from emp

What to_date is doing here ? Anyone have any idea about this query ?

回答1:

So how the query works? Well here’s why:

select to_char(to_date(:number,'j'),'jsp') from dual;

If you look into the inner most part of the query to_date(:number,'j') the ‘j’ or J is the Julian Date (January 1, 4713 BC), basically this date is been used for astronomical studies.

So to_date(:number,'j') it take the number represented by number and pretend it is a julian date, convert into a date.

If you pass 3 to number, so it will convert date to 3rd Jan 4713 BC, it means 3 is added to the Julian date.

select to_char(to_date(3,'j'),'jsp') from dual;

Now to_char(to_date(3,'j'),'jsp'), jsp = Now; take that date(to_date(3,'j')) and spell the julian number it represents, the output is:

TO_CH
-----
three

There is a limitation while using Julian dates ,It ranges from 1 to 5373484. That’s why if you put the values after 5373484, it will throw you an error as shown below:

ORA-01854: julian date must be between 1 and 5373484

Hi everyone, it is interesting this topic. I remember when I was learning Oracle in 2005 one of the instructor required me to write a PL/SQL code to convert numbers in words, it was a whole two pages code to reach this.

Here is some reference that could help us to understand the Julian day, that is why we use the letter 'j' or 'J' during this operation.

First there is a website that has the example and explanation about "How To Convert Number Into Words Using Oracle SQL Query":

http://viralpatel.net/blogs/convert-number-into-words-oracle-sql-query/

Second if you want to know more about "Julian day" go to:

http://en.wikipedia.org/wiki/Julian_day

Third if you want to know more about who proposed the Julian day number in 1583, it was by "Joseph Scaliger":

http://en.wikipedia.org/wiki/Joseph_Justus_Scaliger

It is not make sence for me continue repiting what another author in these websites have made, that is why I just posted the link you can access them and read what you need to understand how query like this works:

SELECT TO_CHAR (TO_DATE (2447834, 'j'), 'jsp') FROM DUAL;

//Output: two million four hundred forty-seven thousand eight hundred thirty-four

回答2:

I've never heard of a DBMS with a built-in function to do as you ask. You'll need a table of number-names, to join to that once per digit using modulo arithmetic, and string concatenation to produce one In_Words column. Plus some logic to eliminate leading zeros. It will take time to write.

回答3:

J stands for Julian day - the number of days since January 1, 4712 BC. The numbers in your table converted to Julian date. JSP spells out the date:

SELECT to_char(SYSDATE,'JSP') AS number_of_days_sinse_4712_BC
  FROM dual
/

回答4:

to convert decimal number into words, you can follow below code

SELECT TO_CHAR(to_date(TRUNC(num),'J'),'Jsp')
  ||' and '
  || TO_CHAR(to_date(to_number(SUBSTR(num-TRUNC(num),instr(num-TRUNC(num),'.')+1)),'J'),'Jsp') Indicator
FROM
  (SELECT &enter_numbr num FROM dual
  );

Hope this will help!!!