oracle 如何用文字显示数值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16123413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
how to display number value in words
提问by Ravi
Q. Display the number value in Words and output should look like this
Q. 在 Words 中显示数值，输出应该是这样的
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_dateis doing here ? Anyone have any idea about this query ?
什么to_date是在这里做什么？有人对这个查询有任何想法吗？
采纳答案by S. Mayol
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.
如果您查看查询 to_date(:number,'j') 的最内部部分，'j' 或 J 是儒略日期（公元前 4713 年 1 月 1 日），基本上这个日期用于天文研究。
So to_date(:number,'j') it take the number represented by number and pretend it is a julian date, convert into a date.
所以 to_date(:number,'j') 它取由 number 表示的数字并假装它是一个儒略日期，转换成一个日期。
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.
如果您将 3 传递给数字，那么它会将日期转换为公元前 4713 年 1 月 3 日，这意味着将 3 添加到儒略日期。
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_char(to_date(3,'j'),'jsp'), jsp = Now; 取那个日期（to_date（3，'j'））并拼写它代表的朱利安数，输出是：
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:
使用 Julian 日期时有一个限制，范围从 1 到 5373484。这就是为什么如果你把值放在 5373484 之后，它会抛出一个错误，如下所示：
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.
大家好，这个话题很有趣。我记得当我在 2005 年学习 Oracle 时，其中一位讲师要求我编写一个 PL/SQL 代码来将数字转换为单词，这是一个完整的两页代码。
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.
这里有一些参考资料可以帮助我们理解儒略日，这就是我们在此操作中使用字母“j”或“J”的原因。
First there is a website that has the example and explanation about "How To Convert Number Into Words Using Oracle SQL Query":
首先有一个网站，上面有关于“如何使用 Oracle SQL 查询将数字转换为单词”的示例和说明：
http://viralpatel.net/blogs/convert-number-into-words-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
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":
第三，如果你想知道更多关于谁在 1583 年提出了儒略日数，那是“Joseph Scaliger”：
http://en.wikipedia.org/wiki/Joseph_Justus_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
//输出：244.7834
回答by James K. Lowden
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.
我从来没有听说过有内置函数可以按你的要求做的 DBMS。您将需要一个数字名称表，使用模算术将每个数字加入一次，并使用字符串连接来生成一个 In_Words 列。加上一些逻辑来消除前导零。需要时间来写。
回答by Art
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:
J 代表儒略日——自公元前 4712 年 1 月 1 日以来的天数。表中的数字转换为儒略日期。JSP 说明日期：
SELECT to_char(SYSDATE,'JSP') AS number_of_days_sinse_4712_BC
FROM dual
/
回答by user_Shilpi
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!!!
希望这会有所帮助！！！
