利用to_timestamp,to_char两个内置函数将范围为(-999999999,999999999)的数值转换为英文表示:
SELECT initcap(to_char(to_timestamp(substr(lpad(abs(123456789), 9, '0'),
1,
3),
'FF3'),
'FFSP') || ' ' ||
to_char(to_timestamp('000' ||
substr(lpad(abs(123456789), 9, '0'), 4),
'FF9'),
'FFSP'))
FROM dual;
--output
One Hundred Twenty-Three Million Four Hundred Fifty-Six Thousand Seven Hundred Eighty-Nine
利用to_timestamp函数和‘FF3', 'FF9'格式参数将数字格式成微秒的格式(9位), 所以123就变成了123000000。对于此参数的解释如下:
Fractional seconds; no radix character is printed (use the X format element to add the radix character). Use the numbers 1 to 9 after FF to specify the number of digits in the fractional second portion of the datetime value returned. If you do not specify a digit, then Oracle uses the precision specified for the datetime datatype or the datatype's default precision.
SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from dual;
然后就是使用to_char的SP参数了。SP顾名思义就是spell out(读出),前面的FF是微妙格式,和to_timestamp保持一致。
所以这条语句对123456789进行两次spell out。
先是123000000,保持百万位正确读取
然后是000456789,千位的读取。
ok
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10336955/viewspace-662957/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10336955/viewspace-662957/