oracle中trunc、tochar

"SQL根本函数tochar,trunc":
关键词:sql 根本 函数 tochar trunc
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中毫秒四舍五入后的返回


虽然没有找到文档支持,但是想


此文来自: 马开东博客 网址: http://www.makaidong.com


来应该八九不离十。














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






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('abc abc中华') 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中无法显示换行
阅读更多
上一篇什么是聚集函数
下一篇ORACLE函数之GREATEST函数详解实例
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭