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
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.
D Day of week (周日1 .. 周六7)
DAY Name of day
DD 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.
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'
-- 自动将结果列表按字段顺序对应排序
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 decode相当于转换后再排序,所以结果值必须是相同类型,且可排序。如
order by decode(a, '2222' , 1 , '1111' , 2 , 3) <=> order by decode(a, '2222' , 1.0 , '1111' , 2 .5, 3.0) <=> order by decode(a, '2222' , 'a' , '1111' , 'b', 'c')
-- 如遇到空值时, 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
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对空字符串操作无效,因此至少必须有' '空格符!
select distinct lpad(selltype, 2 , '0' ) from lccont;
五、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'||'12XXX5869XXXX','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) LEAST 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('ABC abc中华') from dual;------全角变半角
select VSIZE('abc中华') from dual;-----返回字节数
select INSTR('CORPORATE FLOOR','OR',3,2) from dual;----从第3位开始查找第2个'OR'
select replace (WMSYS.WM_CONCAT(num), ',' , ' ' ) from t1;
行列转换中最简单的一种方法。
select 'a' ||chr( 9 )|| 'b' from dual;
select 'a' ||chr( 13 )|| 'b' from dual;