replace 字符串级别的代替
select replace('accdef','cd','gg') from dual;
REPLAC
------
acggef
translate 字符级别的代替
select translate('accdef','cd','gg') from dual;
TRANSL
------
agggef
replace:
语法:REPLACE(char,search_string[,replacement_string])
解释:replace中,每个search_string都被replacement_string所代替
select replace('acdd','cd','ef') from dual;
REPL
----
aefd
如果replacement_string为空或为null,那么所有的search_string都被移除
select replace('acdd','cd') from dual;
RE
--
ad
如果search_string 为null,那么就返回原来的char
select replace('acdd','ef') from dual;
REPL
----
acdd
select replace('acdd','','') from dual;
REPL
----
acdd
select replace('acdd','') from dual;
REPL
----
acdd
translate:
语法:translate(string,from_string,to_string);
to_string 不能为空,为空则反馈null;
SELECT TRANSLATE('我是中国人', '中国', 'China') from dual;
TRANSLAT
--------
我是Ch人
select translate('abcdef','acd','hh') from dual; ----from_string 比to_string 长,多余部分对应null;
TRANS
-----
hbhef
select translate ('abcdef','acd','') from dual; -------返回 空
select translate ('abcdef','','') from dual; -------返回 空