Oracle translate函数
column str format a20;
var str varchar2(200);
exec :str:='abc';
select translate(:str,'abc','123') str from dual;
select translate(:str,'a','') str from dual;
select translate(:str,'abc','1') str from dual;
第一种情况,将映射的字符串修改
第二种情况,函数第三个参数为空串,则返回 null
第三种情况,如果不能完全映射,则不能映射的字符,一律映射为空串.
应用一,计算字符在字符串中出现的次数
var str varchar2(200);
exec :str:='10,20,25';
select regexp_count(:str,',')+1 as cnt from dual;
select length(regexp_replace(:str,'[^,]'))+1 as cnt from dual;
select length(translate(:str,','||:str,','))+1 as cnt from dual;
其中regexp_count在11g中提供
应用二,从字符串中删除不需要的字符
column str format a10;
var str varchar2(200);
exec :str:='CHARK';
select translate(:str,'1AEIOU','1') str from dual;
select regexp_replace(:str,'[AEIOU]') str from dual;
将元音字符删除.
应用三,字符数字分离
column c1 format a10;
column c2 format a10;
var str varchar2(200);
exec :str:='IT10MANAGER';
select
regexp_replace(:str,'[0-9]','') c1,
regexp_replace(:str,'[^0-9]','') c2
from dual;
select translate(:str,'a0123456789','a') c1,
translate(:str,'0123456789'||:str,'0123456789') c2
from dual;
注意:
正则,^不在方括号里时,表示字符串开始.
参考:
Oracle查询优化改写,第五章
column str format a20;
var str varchar2(200);
exec :str:='abc';
select translate(:str,'abc','123') str from dual;
select translate(:str,'a','') str from dual;
select translate(:str,'abc','1') str from dual;
第一种情况,将映射的字符串修改
第二种情况,函数第三个参数为空串,则返回 null
第三种情况,如果不能完全映射,则不能映射的字符,一律映射为空串.
应用一,计算字符在字符串中出现的次数
var str varchar2(200);
exec :str:='10,20,25';
select regexp_count(:str,',')+1 as cnt from dual;
select length(regexp_replace(:str,'[^,]'))+1 as cnt from dual;
select length(translate(:str,','||:str,','))+1 as cnt from dual;
其中regexp_count在11g中提供
应用二,从字符串中删除不需要的字符
column str format a10;
var str varchar2(200);
exec :str:='CHARK';
select translate(:str,'1AEIOU','1') str from dual;
select regexp_replace(:str,'[AEIOU]') str from dual;
将元音字符删除.
应用三,字符数字分离
column c1 format a10;
column c2 format a10;
var str varchar2(200);
exec :str:='IT10MANAGER';
select
regexp_replace(:str,'[0-9]','') c1,
regexp_replace(:str,'[^0-9]','') c2
from dual;
select translate(:str,'a0123456789','a') c1,
translate(:str,'0123456789'||:str,'0123456789') c2
from dual;
注意:
正则,^不在方括号里时,表示字符串开始.
Oracle查询优化改写,第五章
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29254281/viewspace-1816555/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/29254281/viewspace-1816555/