ORACLE将数字转换成英文显示
来源:互联网 发布:唯一网络 私募 编辑:程序博客网 时间:2024/05/01 14:11
Convert Numbers to Words
by Brian Membrey, The PaperBag Software Company Pty Ltd
I guess a mundane exercise that most programmers cop at some stage is having to convert a number (123) into a text equivalent (ONE HUNDRED AND TWENTY THREE) - for cheques, group certificates, etc. Probably the code involved a loop stripping out the numerals and then applying a value according to the relative position within the overall value.
Although it winds a strange path via date functions, SQL*Plus actually provides a mechanism for automating much of this process. Executing the following :
SELECT TO_CHAR ( TO_DATE ( TO_CHAR ( 103465, '99999999999') , 'J'), 'JSP') FROM dual;
returns a value of ONE HUNDRED THREE THOUSAND FOUR HUNDRED SIXTY-FIVE
If we break the statement into each component function, then what happens is :
the inner TO_CHAR simply converts the number (which would probably be a numeric variable in practice) to CHAR so some magic can happen ...
the TO_DATE converts the CHAR using the J (Julian day) format. (the Julian day is the number of days since January 1, 4712BC, which is when SQL*Plus was invented),
having established the date value, we then convert that date back to a Julian day. Because the TO_CHAR in this case is used in DATE context, we can use the J mask to duplicate the original value, and append the SP (spell) format mask. 'Spell" does exactly that - it converts the number to words, hence the string value above.
SP can be used in a number of situations. For example, if SYSDATE is 26-AUG-98, then :
SELECT TO_CHAR ( SYSDATE, 'DdSp') FROM dual; -- spells the day as Twenty-Six,
and
SELECT TO_CHAR ( SYSDATE, 'DDSPTH') FROM dual; --returns TWENTY-SIXTH
Some simple manipulations can be included with the base conversion to cover floating numbers or currencies (email brianm@lt.com.au for source), eg. 103465.27 becomes ONE HUNDRED AND THREE THOUSAND FOUR HUNDRED AND SIXTY-FIVE DOLLARS AND TWENTY-SEVEN CENTS.
One covenant however : if in your mad appreciation of this trivia you want to send me a cheque for more than $5,373,484.00, then you'll have to write it manually, or send more than one cheque!
SQL*Plus restricts Julian days to between 1 and 5373484, which won't be a problem for most applications, but should be borne in mind before using the technique in anger.
5373484 represents 31-Dec-9999, so this may be Oracle's way of introducing us to a Year 10K problem!
EXAMPLE:
select to_char(to_date(1200,'J'),'Jsp') from dual
- ORACLE将数字转换成英文显示
- 编写程序将数字转换成英文
- 将中文数字转换成英文表示
- 数字转换成英文
- 数字转换成英文
- EXCEL 中如何将数字转换成英文
- EXCEL 中如何将数字转换成英文
- EXCEL 中如何将数字转换成英文
- python:将数字转换成用英文表达的程序
- EXCEL 中如何将数字转换成英文
- 将英文字符串转换为数字
- HDU1727数字转换成英文
- Lua封装将数字转换成万显示
- unity编辑器扩展篇-将图片数字和英文转换成字体文件
- 将数字格式的日期月份转换为英文月份
- Oracle中如何将数字转换成字符型
- Oracle中如何将数字转换成字符型
- oracle里存储函数将金额数字转换成大写
- spring中的BeanWrapper,Bean Factory,ApplicationContext
- 使用RMI和CORBA进行分布式java程序设计
- 一个js日期选择器。当光标移到文本框时。自动会在文本框的底部显示出来。
- 冰释前嫌 论新生事物SOAP的误解如何消融
- Visual Studio.Net 快捷键表(转)
- ORACLE将数字转换成英文显示
- 希望我不要下定决心放弃borland与delphi
- XML Spy实例教程(转)
- ArcGis Geodatabase 学习笔记
- 人心深处
- 一些经典言论
- 如何用asp调用dll组件里边的函数??
- 女儿,是父亲上辈子不能在一起的情人
- 《用做好的Excel報表模板來做報表》