SQL基本函数学习笔记(转载)
一、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
union all
select
union all
select
)
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中无法显示换行
类别：数据库 查看评论
