Oracle ~ 常用高级函数
LENGTH
|| 并置运算符
nvl
nvl2
coalesc
replace
SIGN
substr
translate
(from_string和to_string中的字符是一一对应的,然后根据这种对应关系,将原字符串进行转换;
select translate(‘ab123’, ‘abcdefg123’, ‘3456789abc’) from dual;
结果是: 3b5abc)
instr
MySQL: select * from tableName where name like ‘%helloworld%’;
Oracle:select * from tableName where instr(name,‘helloworld’)>0; --这两条语句的效果是一样的
4 select instr(‘helloworld’,‘l’,-1,1) from dual; --返回结果:9 也就是说:在"helloworld"的倒数第1(d)号位置开始,往回查找第一次出现的“l”的位置
select replace(vm_concat(a),’,’’|’) from A group by id
select to_char(vm_concat(a)) from A group by id
trim(to_char( flout ,‘999990.00’))
trim(to_char( flout ,‘999990.00’))
select trim(to_char(round(12522/100,2),‘9999990.99’)) num from dual
lag/lead
select period_id,srv_branch_no,lag(srv_branch_no,1,null) over(partition by srv_branch_no order by srv_branch_no desc) from aiskbus.aisk_client where rownum<=100