–大小写函数
select lower(‘YZW‘)from dual
select upper(‘yzw‘)from dual
–帕斯卡
select initcap(‘yzw six six six‘) from dual
–substr
select substr(‘YZW666‘,1,2) from dual
–3.求字符个数
select length(‘YZW666‘) from dual
–字节个数
select lengthb(‘叶‘) from dual
–求出小字符串在大字符串中出现的位置
select instr(‘YZW666‘,‘Z‘) from dual
–参数
select instr(‘AORBORCORDOREOR‘,‘OR‘,-3,2) from dual
–lpad()
select lpad(‘Happy‘,10,‘‘) from dual
select rpad(‘Happy‘,10,‘‘) from dual
–trim()
select trim(‘ A BC ‘) from dual
select trim(‘a‘ from ‘a BCa‘) from dual
–日期函数
months_between 两个日期相差的月数
select months_between(sysdate,to_date(‘1999-05-05‘,‘yyyy-mm-dd‘)) from dual;
–日期相减
–按天数
select floor(sysdate-to_date(‘05-05-1999‘,‘MM-DD-YYYY‘)) from dual
select floor(sysdate-to_date(‘1999-05-05‘,‘yyyy-mm-dd‘)) from dual
–显示转换
select to_char(sysdate,‘yyyy-mm-dd hh24:mi:ss‘) from dual
select to_number(‘12.23‘) from dual
select ‘12.23‘+‘23.34‘ from dual
select to_char(sal,‘L999,999.9‘) from emp
select * from emp
–数字函数
select round(12.55,0) from dual
select trunc(12.99,1) from dual
–滤空函数
select ename,sal+nvl(comm,0) as 薪资 from emp;
select ename,sal+nvl2(comm,comm*2,0) as 薪资 from emp
–decode函数
select ename,decode(deptno,10,‘开发部‘,20,‘测试部‘,30,‘财务部‘,40,‘保洁部‘,‘人事部‘) as 部门 from emp