内置函数
select abs(-100) from dual;
select mod(22,3 ) from dual;
select ceil(12.0)from dual; select floor(12.5)from dual;
select round(12.445 ,2) from dual; --截取 select trunc(12.345,2)from dual;
select xingm, length(xingm)from t_hq_ryxx;
--截取字符串
select xingm, substr(xingm,1,2 )from t_hq_ryxx;
select xingm, substr(xingm,-2,2 )from t_hq_ryxx; select xingm, substr(xingm,-3,1 )from t_hq_ryxx;
--字符串连接
select concat('dasd','dsada' )from dual;
select concat('dasd' ,concat('sadas', 'dsada') )from dual;
select 'sdas'||'sadas'||'sdas' from dual;
select instr('abdcdefdg','d')from dual;
--从第三个开始找
select instr('adbcdefgd','d',3)from dual;
select instr('abdcdefdg','dd')from dual;
select upper ('asdrgcvb'), lower('SHJASDKJ'),initcap('wo cao ni mei') from dual;
select replace ('sdjsadhkja','sa','123') from dual;
--更新替换
update T_HQ_RYXX t set xingm = replace(xingm,'李','赵') where xingm like '李%';
--填充
select rpad('aa',8,'c')from dual;
select rpad('aa',8)from dual;
select lpad('aa',8,'c')from dual;
--去空格
select trim (' dasdas ') from dual;
select ltrim (' dasdas ') from dual;
select rtrim (' dasdas ') from dual;
select trim (leading'a' from 'aasdsd') from dual;
select trim (trailing'a' from 'aasdsda') from dual;
select trim (both'a' from 'aasdsda') from dual;
--当前时间
select sysdate from dual;
--月份加减
select add_months(sysdate,2) from dual;
select add_months(sysdate,-2) from dual;
select last_day(sysdate) from dual;
select last_day(sysdate)+ 10 from dual;
select cast('123' as number) +123 from dual;
select cast(12345 as varchar2(5)) from dual; select cast(12345 as varchar2(5))+ 123 from dual;
select cast (sysdate as varchar2(20)) from dual;
--日期转换
select to_char(sysdate,'YYYY-DD-MM') from dual
select to_char(sysdate,'yy-mm-dd hh:mi:ss') from dual
select to_char(sysdate,'yy-mm-dd hh24:mi:ss') from dual
select to_char(123.456,'9999.9') from dual;
select to_date('2015-11-11 12:33:45','yyyy-mm-dd hh24:mi:ss')from dual;
update t_hq_ryxx set ruzrq = to_date('2015-12-31','yyyy-mm-dd')where bianh = '104';
select t.*, t.rowid from T_HQ_RYXX t where ruzrq > to_date('2015-10-11','yyyy-mm-dd');
select to_number ('123.456' ,'99999.99999')from dual;
--年龄为空的替换为20
select nvl(nianl,20) from t_Hq_Ryxx;
select nvl(nianl,0) + gongz from t_Hq_Ryxx;
--不为空替换为20,为空替换为1000
select nvl2(nianl,20,1000) + gongz from t_Hq_Ryxx;
--区查处结果的相反结果
select * from t_hq_ryxx where lnnvl(nianl > 20);