oracle常用函數介紹

字元函數

數學函數

日期函數

轉換函數

to_char(數字/日期)是把日期或數字轉換為字元串

• 将數字轉換成指定格式

to_char(1210.73, '9999.9')           -- would return '1210.7'
to_char(1210.73, '9,999.99')      -- would return '1,210.73'
to_char(1210.73, '$9,999.00')   --  would return '$1,210.73'
to_char(21, '000099')           -- would return '000021'           

複制

• 将日期轉換成指定格式

to_char(sysdate, 'yyyy/mm/dd');--would return '2003/07/09'
select to_char(sysdate, 'yyyy-mm-dd  hh24:mi:ss')  from a;    ------> 2015-08-28  14:43:15
select to_char(sysdate, 'yyyy-mm-dd  hh12:mi:ss')  from a;    ------> 2015-08-28  02:43:15
to_char(sysdate, 'yyyy/mm/dd');--would return '2003/07/09'
to_char(sysdate,'dd-mm-yyyy')--按dd-mm-yyyy格式轉換23-05 -2016
to_char(sysdate, 'Month DD, YYYY');--would return '10月 21, 2015'
to_char(sysdate, 'FMMonth DD, YYYY');--would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY');--would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');--would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');--would return 'Jul 9th, 2003'
to_char(sysdate, 'FMMonth DD, YYYY');--would return 'July 9, 2003'
to_char(sysdate, 'FMMON DDth, YYYY');--would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY');--would return 'Jul 9th, 2003'           

複制

to_number(字元串) 将給出的字元轉換為數字

select to_number('123') from a ;             

複制

正常函數