oracle常用函数

###常用的单行函数：
#null值处理
nvl(expr1,expr2) 处理NULL值
nvl2 (exp1, exp2, exp3) 为：如果exp1为NULL，则函数返回exp3，否则返回exp2
decode (expression, search_1, result_1, search_2, result_2, …, search_n, result_n, default)
select * from ywgl_user
order by (case username
when null then
‘李四’
else
username
end);

select nvl(null,123) from dual;
select nvl(1,123) from dual;

select nvl2(null,2,3) from dual;
select nvl2(1,2,3) from dual;

select decode(null,null,11,2,22) from dual;
select decode(1,1,11,2,22) from dual;
select decode(null,1,11,2,22,99) from dual;

select case 1 when 1 then 2 else 3 end from dual;
–1.字符函数;
–1.1大小写转换函数 upper lower
–initcap将每个单词首字母大写，其他小写，单词与单词之间用空格分离
select lower(‘hello worLD’)from dual;
select upper(‘hello worLD’)from dual;
select initcap(‘hello worLD’)from dual;

–2字符串连接函数concat(exp1,exp2)只能
select concat(‘hello’,‘world’)from dual;

–3截取字符串获得子字符串的函数substr(exp ，m，n)orcl字符串下标从1开始的
–如果m<0从字符末尾第绝对值m倒着开始截取n个
select substr(‘you win a game’ ,0,3)from dual;
select substr(‘you win a game’ ,2,3)from dual;
select substr(‘you win a game’ ,-9,4)from dual;

–length
–instr（exp1,m）获取子字符串在字符串中的位置
select instr(‘you or ni’ ,‘or’)from dual;

–补位函数 lpad rpad(exp1,n,exp2)如果n小于lpad的长度时，相当于截取
select lpad(‘78999’,10,‘1’) from dual;
select rpad(‘78999’,10,‘1’) from dual;
select rpad(‘78999’,1,‘1’) from dual;

–replace（exp,old,new）
select replace(‘efsfdds’,‘sf’,‘ee’)from dual;
select replace(‘efsfddsf’,‘sf’,‘ee’)from dual;

–trim(c2 from c1)截去子串函数,
–ltrim (c1,c2)字符串中第一个字符和最后一个字符是要被去掉的如果没参数则trim（）有去空的作用。
select trim(‘圆’ from ‘圆更圆是什么东圆’)from dual;
select ltrim(‘圆更圆是什么东东圆’,‘圆’)from dual;
select rtrim(‘圆更圆是什么东东圆’,‘圆’)from dual;

–数学函数
–1：trunc round 截取位数函数
select trunc(23.576,2) from dual;
select round(25.5,-1) from dual;

–日期函数
–1add_months(date1, month)
select add_months(‘2016/7/8’,2) from dual;
select add_months(‘2016/7/8’,-2) from dual;

–round trunc 截取日期
select to_char(round(sysdate,‘MONTH’),‘MM’)from dual;
select to_char(trunc(sysdate,‘MONTH’),‘MM’)from dual;

–转换函数 to_char() ,to_date()Dy是星期几
select to_char(sysdate,‘YYYY"年"MM"月"DD"日"DY’)from dual;
select to_date(‘1993-3-4’,‘YYYY-MM-DD’)from dual;