本人为oracle菜鸟,
select 500 / 1000 from dual
显示:0.5
select to_char(500 / 1000) from dual
显示:.5我想请教,ORACLE中如何将数字转化为字符串能够正确显示出0.5例:select to_char(500 / 1000,'0.0') from dual
这样结果是为 0.5
但是当select to_char(5000 / 1000,'0.0') from dual
这是的结果是 5.0 (这样是有问题的,我需要的是5不要后面的.0)但是当select to_char(5000 / 1000,'0.00') from dual
这是的结果是 5.00 (这样是有问题的,我需要的是5不要后面的.00)但是当select to_char(500 / 1000,'0.00') from dual
这是的结果是 0.50(这样是有问题的,我需要先显示的是0.5而不是0.50)
请教该如何实现呢?
select 500 / 1000 from dual
显示:0.5
select to_char(500 / 1000) from dual
显示:.5我想请教,ORACLE中如何将数字转化为字符串能够正确显示出0.5例:select to_char(500 / 1000,'0.0') from dual
这样结果是为 0.5
但是当select to_char(5000 / 1000,'0.0') from dual
这是的结果是 5.0 (这样是有问题的,我需要的是5不要后面的.0)但是当select to_char(5000 / 1000,'0.00') from dual
这是的结果是 5.00 (这样是有问题的,我需要的是5不要后面的.00)但是当select to_char(500 / 1000,'0.00') from dual
这是的结果是 0.50(这样是有问题的,我需要先显示的是0.5而不是0.50)
请教该如何实现呢?
解决方案 »
- 建了一个DML触发器,插入数据的时候触发,操作时报错,求解答。。。
- oracle目录结构问题
- 我用asp连接oracle92数据库,怎么都不行,是不是缺少什么东西,请有经验的朋友帮助?
- OMS的配置问题
- rac中几个节点时间不同步了,直接修改时间使他们一致,对数据库会不会造成影响啊
- 有关Logminer与对象被使用的问题?
- 程序中执行某个sql语句,结果出现“用户请求取消当前的操作”
- 不小心删除服务下的一个表空间(tablespace),导致整个服务无法启动。请大侠帮忙!
- 有关ORACLE的汉字拼音问题
- 无语了,,安装Oracle报空引用异常。。。。急求大神们支招
- 无法用sys登录
- Oracle 创建用户 却赋予不了权限
FM :除空格
9999999.0099:允许小数点左边最大正数为7位,小数点右边最少2位,最多4位,且在第5位进行四舍五入
*/
Select TO_CHAR(123.0233,'FM9999999.0099') FROM DUAL例如
SQL> select to_char(5000 / 1000,'fm9.99') from dual;TO_CHAR(5000/1000,'FM9.99')
---------------------------
5.
---------------------------
0.5
这样说比较理想的情况...
select round(5000/1000,1) from dual
select to_char(round(500/1000,3)) from dual
结果:.5 还是不对啊
结果:0.01 不对啊
select to_char(300 / 10,'fm90.99') from dual;
结果:30. 不对啊
select to_char(3000 / 10,'fm90.99') from dual;
结果:###### 不对啊
请高手们再给看一下吧
不过其实冲突就是在不使用fmt参数,并且结果小于的时候没显示小数点前的0
这样不如在小于的时候直接加个0在前面好了 ^_^比如我想到的最笨的办法:
SQL> select decode(sign(500/1000-1),-1,'0'||to_char(500/1000),to_char(500/1000)) from dual;
DECODE(SIGN(500/1000-1),-1,'0'
------------------------------
0.5
SQL> select decode(sign(5000/1000-1),-1,'0'||to_char(5000/1000),to_char(5000/1000)) from dual;
DECODE(SIGN(5000/1000-1),-1,'0
------------------------------
5
Text description of to_char_number
Purpose
TO_CHAR (number) converts n of NUMBER datatype to a value of VARCHAR2 datatype, using the optional number format fmt. If you omit fmt, then n is converted to a VARCHAR2 value exactly long enough to hold its significant digits.The 'nlsparam' specifies these characters that are returned by number format elements:Decimal character
Group separator
Local currency symbol
International currency symbol
This argument can have this form:'NLS_NUMERIC_CHARACTERS = ''dg''
NLS_CURRENCY = ''text''
NLS_ISO_CURRENCY = territory '
The characters d and g represent the decimal character and group separator, respectively. They must be different single-byte characters. Within the quoted string, you must use two single quotation s around the parameter values. Ten characters are available for the currency symbol.If you omit 'nlsparam' or any one of the parameters, then this function uses the default parameter values for your session.See Also:
"Format Models" for information on number formats
Examples
The following statement uses implicit conversion to interpret a string and a number into a number:SELECT TO_CHAR('01110' + 1) FROM dual;
TO_C
----
1111
Compare this example with the first example for TO_CHAR (character).In the next example, the output is blank padded to the left of the currency symbol.SELECT TO_CHAR(-10000,'L99G999D99MI') "Amount"
FROM DUAL;Amount
--------------
$10,000.00-SELECT TO_CHAR(-10000,'L99G999D99MI',
'NLS_NUMERIC_CHARACTERS = '',.''
NLS_CURRENCY = ''AusDollars'' ') "Amount"
FROM DUAL;Amount
-------------------
AusDollars10.000,00-
--------------------------------------------------------------------------------
Note:
In the optional number format fmt, L designates local currency symbol and MI designates a trailing minus sign. See Table 2-13 for a complete listing of number format elements.
select rtrim(to_char(.5,'fm0.99'),'.') from dual;
select rtrim(to_char(.05,'fm0.99'),'.') from dual;
select rtrim(to_char(.5,'fm0.99'),'.') from dual;
select rtrim(to_char(.05,'fm0.99'),'.') from dual;
select rtrim(to_char(.5,'fm0.99'),'.') from dual;
select rtrim(to_char(.05,'fm0.99'),'.') from dual;
这样也是不正确的啊
select rtrim(to_char(500000/10000,'fm0.99'),'.') from dual;
结果:#####
select rtrim(to_char(500/100000,'fm0.99'),'.') from dual;
结果:0.01