translate的主要作用是提取,替换字符串,其作用有时候和replace差不多,translate是一个一个字符替换,replace是一段字符替换
translate中有“#”的特殊用法,以#开头的表示所有字符
- select translate('liyan4h123ui','#liyanhui','#') from dual
- 结果:4123
- select translate('liyan4h123ui','#liyanhui','#z') from dual;
- 结果:z4123
- select translate('liyan4h123ui','#liyanhui','#zx') from dual;
- 结果:zx4123x
- select translate('asadad434323', '#0123456789','#') from dual ;
- 结果:asadad
select translate('用纸箱包200PCS/箱',REGEXP_replace('用纸箱包200PCS/箱','[0-9]',''),' ')from dual;
select translate('用纸箱包200PCS/箱',regexp_replace('用纸箱包200PCS/箱', '\d+PCS', ''),' ') from dual
select '515.123.4567', REGEXP_REPLACE('515.123.4567','([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1)\2-\3') "PHONE_NUMBER"
from dual t;
select substr('天津市河东区379#-888室',instr('天津市河东区379#-888室','#')-3,3) from dual;