oracle中trunc、tochar

转载 2018年04月16日 22:33:24
"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 TRUNC()函数的用法

Oracle TRUNC()函数的用法
  • qq_26676207
  • qq_26676207
  • 2016-10-27 16:56:25
  • 1290

Oracle TRUNC 函数详解

1.TRUNC 函数 Oracle TRUNC函数可以截取数字和日期类型:   2.TRUNC截取数字 TRUNC(number)函数返回n1截取到n2位小数。如果省略n2时,则n1截取到0位置(即截...
  • chiclewu
  • chiclewu
  • 2014-01-04 21:41:55
  • 3094

oracle内置函数 trunc 使用

trunc 是 truncate 的简写。 中文翻译是 “截断”或 “截短”。 表面看上去,应该是对字串或是数字进行截取。 实际上这个函数有两种用法, 而且这两种用法看上去很不一样。 1. 对...
  • oscar999
  • oscar999
  • 2013-11-20 15:07:44
  • 3898

Oracle中trunc()函数的用法

(1)处理日期:TRUNC函数为指定元素而截去的日期值 格式:trunc(date,[format]) select trunc(sysdate) from dual; --2013-12-1...
  • u012736409
  • u012736409
  • 2013-12-15 20:20:40
  • 1319

oracle中trunc函数的简单使用

oracle中的trunc()函数是对时间类型或者数字进行截取操作的。 一般用法为trunc(Date,“fmt”),其中fmt 是我们要展示的时间字段,或者trunc(number),该函数表示取...
  • Aeyewp
  • Aeyewp
  • 2017-04-25 14:47:37
  • 2951

Oracle Trunc和ROUND区别

1、关于trunc 和round函数比较
  • damaolly
  • damaolly
  • 2014-05-08 13:55:13
  • 3450

mysql中跟oracle的trunc函数的对应

select  DATE_FORMAT(now(), '%Y-%m-%d %H:%i:%s');
  • John_Chang11
  • John_Chang11
  • 2016-10-20 16:28:29
  • 5178

Oracle中round()函数与trunc()函数的区别?

round(x[,y])  【功能】返回四舍五入后的值  【参数】x,y,数字型表达式,如果y不为整数则截取y整数部分,如果y>0则四舍五入为y位小数,如果y小于0则四舍五入到小数点向左第y位。 ...
  • u013456370
  • u013456370
  • 2016-11-25 16:32:02
  • 1804

Oracle中删除表操作trunc delete和drop的说明

相同点: 1.truncate和不带where子句的delete, 以及drop都会删除表内的数据。 2.drop,truncate都是DDL语句,执行后会自动提交。 不同点: 1. tr...
  • SuperCrrazy
  • SuperCrrazy
  • 2018-01-23 13:08:18
  • 90

Oracle to_date/Trunc函数用法及date字段只精确到日期的问题

本文主要涉及三个问题:to_tate函数、Trunc函数的使用方法及date字段数据只获取日期不获取时间的问题 1、两个函数的使用方法: (1)to_date() 用法:TO_DATE('2016...
  • zcf_0923
  • zcf_0923
  • 2016-04-18 18:45:42
  • 3757
收藏助手
不良信息举报
您举报文章:oracle中trunc、tochar
举报原因:
原因补充:

(最多只允许输入30个字)