TO_CHAR 数值转字符语法:
TO_CHAR(number,'format_model')
9 | 数字(代表任意数字) |
0 | 零 |
$ | 美元符 |
L | 本地货币符 |
. | 小数点 |
, | 千位符 |
TO_CHAR 数值转字符用法;
centos@SCOTT> SELECT TO_CHAR(123.456,'99999.99') AS num FROM dual;
NUM
---------
123.46
centos@SCOTT>
9表示一个数字,如小数点后显示的位数小于实际数字,那么在显示时会四舍五入。
使用fm去除空格;
centos@SCOTT> SELECT REPLACE(TO_CHAR(123.456,'99999.99'),' ','#') AS num FROM dual;
NUM
---------
###123.46
centos@SCOTT> SELECT REPLACE(TO_CHAR(123.456,'fm99999.99'),' ','#') AS num FROM dual;
NUM
---------
123.46
centos@SCOTT>
使用0补齐要求宽度;
centos@SCOTT> SELECT TO_CHAR('123','09999999') AS zero FROM dual;
ZERO
---------
00000123
centos@SCOTT>
TO_CHAR 日期转字符语法:
TO_CHAR(date,'format_model')
TO_CHAR 日期转字符用法;
centos@SCOTT> SELECT TO_CHAR(SYSDATE,'yyyy-mm-dd hh24:mi:ss') AS dt FROM dual;
DT
-----------------------
2017-04-01 12:23:43
centos@SCOTT>
使用fm去除前导零;
centos@SCOTT> SELECT TO_CHAR(SYSDATE,'fmyyyy-mm-dd hh24:mi:ss') AS dt FROM dual;
DT
-------------------
2017-4-1 12:26:5
centos@SCOTT>
使用scc或cc显示世纪;
centos@SCOTT> SELECT TO_CHAR(SYSDATE,'scc yyyy-mm-dd hh24:mi:ss') AS dt FROM dual;
DT
-----------------------
21 2017-04-01 12:27:30
centos@SCOTT> SELECT TO_CHAR(SYSDATE,'cc yyyy-mm-dd hh24:mi:ss') AS dt FROM dual;
DT
----------------------
21 2017-04-01 12:27:57
centos@SCOTT>
使用yyyy显示年;
centos@SCOTT> SELECT TO_CHAR(SYSDATE,'yyyy') AS dt FROM dual;
DT
----
2017
centos@SCOTT>
使用year显示英语单词拼写的年;
centos@SCOTT> SELECT TO_CHAR(SYSDATE,'year') AS year FROM dual;
YEAR
------------------------------------------
twenty seventeen
centos@SCOTT>
使用q显示当前年的第几季度;
centos@SCOTT> SELECT TO_CHAR(SYSDATE,'q') AS q,SYSDATE FROM dual;
Q SYSDATE
- ----------
2 2017-04-01
centos@SCOTT>
使用ww显示一年中第几周,w显示一个月中第几周;
centos@SCOTT> SELECT TO_CHAR(SYSDATE,'ww') AS ww,SYSDATE FROM dual;
WW SYSDATE
-- ----------
13 2017-04-01
centos@SCOTT> SELECT TO_CHAR(SYSDATE,'w') AS w,SYSDATE FROM dual;
W SYSDATE
- ----------
1 2017-04-01
centos@SCOTT>