运维工程师常用的Oracle经典语句——2.Oracle常用的字符串及日期函数

1. 常用字符串函数

--字符函数
select
		 	substr('abcdefg',1,5)substr,                     --字符串截取
       		instr('abcdefg','bc') instr,                     --查找子串
      		'Hello'||'World' concat,                         --连接
       		trim('  wish  ') trim,                           --去前后空格
       		rtrim('wish  ') rtrim,                           --去后面空格
       		ltrim('  wish') ltrim,                           --去前面空格
       		trim(leading 'w' from 'wish') deleteprefix,      --去前缀
       		trim(trailing 'h' from 'wish') deletetrailing,   --去后缀
       		trim('w' from 'wish') trim1,
       		ascii('A') A1, 
       		ascii('a') A2,                                   --ascii(转换为对应的十进制数)
       		chr(65) C1, 
       		chr(97) C2,                                      --chr(十进制转对应字符)
       		length('abcdefg') len,                           --length 
      		lower('WISH')lower, 
       		upper('wish')upper, 
       		initcap('wish')initcap,                          --大小写变换
      		replace('zhangsan','1','lisi') replace,          --替换
       		translate('zhangsan1','1','y')translate,         --转换，对应一位(前面的位数大于等于后面的位数)
       		translate('wish1','sh1','hy')translate1,
       		concat('11','22') concat          　　　　　　　　   --连接
from dual;

--to_number
--to_number(expr)
--to_number(expr,format)
--to_number(expr,format,'nls-param')

2. 常用日期函数

--日期
--年 yyyy yyy yy year
--月 month mm mon month
--日+星期  dd ddd(一年中第几天) dy day 
--小时  hh hh24 
--分 mi
--秒 ss

select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss')currenttime, 
       to_char(sysdate,'yyyy') year,
       to_char(sysdate,'mm') month,
       to_char(sysdate,'dd') day,
       to_char(sysdate,'day') week,
       to_char(sysdate,'hh24')hour,
       to_char(sysdate,'mi') minute,
       to_char(sysdate,'ss') second
from dual;

select to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss')currenttime,
       to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'yyyy')year,
       to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'mm')month,
       to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'dd') day,
       to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'day') week,
       to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'day','NLS_DATE_LANGUAGE=American') week, --设置语言
       to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'hh24')hour,
       to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'mi') minute,
       to_char(to_date('2009-07-04 05:02:01','yyyy-mm-dd hh24:mi:ss'),'ss') second
from dual;

--months_between
 select months_between(to_date('03-31-2014','MM-DD-YYYY'),to_date('12-31-2013','MM-DD-YYYY')) "MONTHS"
 FROM DUAL;

--next_day
select sysdate today, next_day(sysdate,6) nextweek from dual;

--时间区间
select cardid, borrowdate from borrow where to_date(borrowdate,'yyyy-mm-dd hh24:mi:ss')  
between 
to_date('2014-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss') and 
to_date('2014-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss');  

--interval 间隔
select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') currenttime,
       to_char(sysdate - interval '7' year,'yyyy-mm-dd hh24:mi:ss') intervalyear,   
       to_char(sysdate - interval '7' month,'yyyy-mm-dd hh24:mi:ss') intervalMonth,   
       to_char(sysdate - interval '7' day,'yyyy-mm-dd hh24:mi:ss') intervalday,   
       to_char(sysdate - interval '7' hour,'yyyy-mm-dd hh24:mi:ss') intervalHour,   
       to_char(sysdate - interval '7' minute,'yyyy-mm-dd hh24:mi:ss') intervalMinute,   
       to_char(sysdate - interval '7' second,'yyyy-mm-dd hh24:mi:ss') intervalSecond  
from dual;  


--add_months 增加月份
select add_months(sysdate,12) newtime from dual;

--extract
select extract(month from sysdate) "This Month",
extract(year from add_months(sysdate,36)) " Years" from dual;