oracle常用内置函数

/********************************日期类函数**********************************/
--当前月份加2个月:add_months
select add_months(sysdate, 2) from dual
select add_months(to_date('2018-12-12', 'yyyy-MM-dd'), 2) from dual

--指定日期和当前日期相差月份：months_between
select months_between(sysdate, to_date('2019-12-12', 'yyyy-MM-dd')) from dual

--返回月份的最后一天:last_day
select to_char(last_day(sysdate), 'yyyy-MM-dd') from dual

--返回当前日期对应下个星期二的next_day
select next_day(sysdate, '星期一') from dual

--获得本月的第一天
select trunc(sysdate, 'MM') from dual
select trunc(to_date('2019-12-12', 'yyyy-MM-dd'), 'MM') from dual

--截取时间值:extract
select extract(year from sysdate) from dual
select extract(month from sysdate) from dual
select extract(day from sysdate) from dual


/***********************************************字符串****************************************************/
--获取字符串长度
select length(' admin ') from dual
--去除两边的空格
select length(trim(' admin ')) from dual
--设置单词首字母大写：initcap
select initcap('admin') from dual
--转换大小写:lower upper
select lower('ADMIN')from dual
select upper('admin') from dual
--去除左右两边的空格: trim, rtrim, ltrim
select rtrim(ltrim(' admin ')) from dual
select trim(' adm in ') from dual
--替换:translate
select translate('hello', 'e', 'v') from dual
select translate('hello', 'll', 'v') from dual
--替换：replace
select replace('hello', 'll', 'v') from dual
--查找字符首次出现的位置:instr
select instr('admini', 'i') from dual
select instr('helloworld' ,'l') from dual;
--截取字符串:substr
select substr('hello', 2, 3) from dual
--字符串拼接:concat
select concat('hello', 'world') from dual
--将字符转换为ASCII码值
select ASCII('b') from dual
--将ASCII码值转换为字符chr
select chr(122) from dual
--向原字符左边/右边填充字符:lpad, rpad
select lpad('hello', 9, 'world') from dual
select rpad('hello', 9, 'world') from dual
--decode: 3 + 2是否等于5?true:false
select decode(3 + 2, 3, 'true','false') from dual