SQL基本函数（to_char,trunc）

一、to_char函数

Parameter    Explanation
YEAR           Year spelled out
YYYY           4 digits of year
YYY            3 digits of year
YY             2 digits of year
Y              1 digit of year
IYYY           4digits year based on the ISO standard
IYY            3 digits of ISO year
IY             2 digits of ISO year
I              1 digit of ISO year
Q              Quarter of year (1 .. 4)
MM             Month (01 ..12)
MON            Abbreviated name of month
MONTH          Name of month, padded with blanks to length of 9 characters.
RM             Roman numeral month (I .. XII)
WW             Week of year (1-53) where 7 days 1 week (与星期几无关)
W              Week of month (1-5) where 7 days 1 week (与星期几无关)
IW             Week of year (1-52 or 1-53) based on the ISO standard.
               (周一到周日为一周，若1日为周五-周日，则为上年最后一周)
D              Day of week (周日1 .. 周六7)
DY             Abbreviated name of day.
DAY            Name of day
DD             Day of month (1-31)
DDTH           Day of month (1-31)
DDD            Day of year (1-366)
J              Julian day；the number of days since January 1, 4712 BC.
HH             Hour of day (1-12).
HH12           Hour of day (1-12).
HH24           Hour of day (0-23).
MI             Minute (0-59).
SS             Second (0-59).
SSSSS          Seconds past midnight (0-86399).
FF             Fractional seconds.
XXXXX          转换为8进制

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(21, '999999')            would return '    21'
to_char(21, 'FM999999')          would return '21'
to_char(sysdate, 'FMYYY')        would return '8'          --FM表示去掉0或空格 

to_char(125, 'XXXXX')            would return '7D'
to_number('7D','XXXXX')          would return '125'


另注：trunc与to_char的比较

trunc原意为截取数据小数部分，例如：

trunc(23.48429387)    返回23
trunc(23.48429387,3)  返回23.484
trunc(-1.443432)      返回-1

但trunc(date) 具有与to_char(date) 相似的功能，但有区别：

trunc(sysdate,'cc')   取当世纪的第一天     to_char(sysdate,'cc')   取当世纪数值
trunc(sysdate,'yyyy') 取当年的第一天       to_char(sysdate,'yyyy') 取当年数值
trunc(sysdate,'iyyy') 取上年的最后一天     to_char(sysdate,'iyyy') 取当年数值
trunc(sysdate,'q')    取当季第一天         to_char(sysdate,'iyyy') 取当季数值
trunc(sysdate,'mm')   取当月第一天         to_char(sysdate,'mm')   取当月数值
trunc(sysdate,'ww')   取当周第一天(周二)   to_char(sysdate,'ww')   取当周数值(第几周)
trunc(sysdate,'iw')   取当周第一天(周一)   to_char(sysdate,'iw')   取当周数值(第几周)

总结：trunc对日期的截取由后面参数决定位置后将之后所有数值为默认初始值！



二、order by函数


-- 自动将结果列表按字段顺序对应排序

order by 1 , 2 , 3



-- 可对字段 decode 后再排序，下例为将 2222 、 1111 排在前两位，其他按顺序排列

select a,b, c from t1

order by decode(a, '2222' , 1 , '1111' , 2 ,a)



-- 如遇到空值时， order by 默认将空值排在最下面，如要排在最上面，则：

order by nulls first



三、取整类函数整理


ceil ： 取整 ( 大 )

    select ceil (- 1.001 ) value from dual    /- 1

floor ：取整（小）

    select floor(- 1.001 ) value from dual    /- 2

trunc ：取整（截取）

    select trunc(- 1.001 ) value from dual    /- 1

round ：取整 ( 舍入 )

select round(- 1.001 ) value from dual    /- 1

应用举例：(根据时间算年龄)
trunc(months_between( sysdate ,birthday)/ 12 ) Age


四、LPAD与RPAD的用法：

比较：select LPAD('WhaT is tHis',5),LPAD('WhaT is tHis',25),LPAD('WhaT is tHis',25,'-') from dual;
            |WhaT|               WhaT is tHis| -------------WhaT is tHis
     select RPAD('WhaT is tHis',5),RPAD('WhaT is tHis',25),RPAD('WhaT is tHis',25,'-') from dual;
            |WhaT|  WhaT is tHis             | WhaT is tHis-------------

作用：作为调整格式的输出，例：



with x as

( select 'aa' chr from dual

union all

select 'bb' chr from dual)

select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 3



说明：若LPAD对空字符串操作无效，因此至少必须有' '空格符!


LPAD的实际应用：



select distinct lpad(selltype, 2 , '0' ) from lccont;

由于系统中其他的selltype字段均为01、02等2位，但出现7，另有null值
所以使用 lpad(selltype,2,'0') 可以即保存null值又将7更新为07




五、rank() order by()和row_number() order by()的区别：



with t as (

select 1 a from dual

union all

select 2 a from dual

union all

select 1 a from dual

)

select a,rank() over( order by a) rank,row_number() over( order by a) num from t;







六、translate和replace的区别：



select translate('What is this','ait','-*%') from dual;---Wh-% *s %h*s

selectreplace('What is this','ait','-*%') from dual;-----What is this

selectreplace('What is this','hat','-*%') from dual;-----W-*% is this



translate的实际应用：

select translate('12XXX5869XXXX','0123456789'||'XXX5869XXXX','0123456789')from dual;

<取字符串中的所有数字>







七、sysdate与current_date的差别：



select sysdate,current_date from dual;

某些情况下current_date会比sysdate快一秒。

我们认为current_date是将current_timestamp中毫秒四舍五入后的返回

虽然没有找到文档支持，但是想来应该八九不离十。







八、一些有用的时间函数：



select NEXT_DAY(sysdate,5) from dual;--下一个星期四(不算今天)

select NEXT_DAY(sysdate,'星期三') from dual;--下一个星期一(大小写都可)

select LAST_DAY(sysdate) from dual;--当月最后一天







九、一些有用的数字/字符函数：



select GREATEST(a,b) Greatest from t2;----------求最大值

select LEAST(a,b) Greatest from t2;-------------求最大值

select NULLIF('a','b'),NULLIF('a','a') from dual;-------a=b则返回null；a<>b则返回a

select nvl(null,'a'),nvl('1','a') from dual;------------为null时返回a，不会null返回原值

select nvl2(null,'a','b'),nvl2('1','a','b') from dual;--为null时返回b，不为null返回a

selectCOALESCE(null,5,6,null,9) from dual;-----返回第一个非空值

select POWER(2.2,2.2) from dual;  ----a的b次方






十、一些有用的字符串操作函数：



select CHR(95) from dual;-------------ASCII码对应字符

select ASCII('_') from dual;----------字符对应ASCII码

select concat('aa','bb') from dual;------------等同于||

select INITCAP('whaT is this') from dual;------首字母大写，其余小写

select TO_MULTI_BYTE('ABC abc　中华') from dual;----------半角变全角

select TO_SINGLE_BYTE('ＡＢＣ　ａｂｃ中华') from dual;------全角变半角

select VSIZE('abc中华') from dual;-----返回字节数

select INSTR('CORPORATE FLOOR','OR',3,2) from dual;----从第3位开始查找第2个'OR'






十一、WMSYS.WM_CONCAT函数应用：

此函数作用在于将某字段所有值列出到一个单元格中


select replace (WMSYS.WM_CONCAT(num), ',' , ' ' ) from t1;



行列转换中最简单的一种方法。





十二、单元格内文本换行的方法：

Tab键 chr(9)
换行符chr(10)
回车符chr(13)
空格符chr(32)

select 'a' ||chr( 9 )|| 'b' from dual;

select 'a' ||chr( 13 )|| 'b' from dual;
注：须在SQLPlus中查看结果，PL/SQL Developer中无法显示换行








-The End-