1.replace 函數(shù)

語法：replace(char, search_string, replacement_string) --針對(duì)字符串替換

功能：

? 將char中的字符串替換。

? 當(dāng)replacement_string為空時(shí)，剔除search_string。

select replace('fasdfasdf','fk','j') as col from dual;
-- fasdfasdf
select replace('fdasfasdd','as','jjj') as col from dual;
-- fdjjjfjjjdd

完全匹配的字符才會(huì)進(jìn)行替換，如無字符匹配，則返回原字符串。

該匹配是貪婪匹配，匹配都的字符串都會(huì)被替換，不僅僅是第一個(gè)匹配到的字符串。

2.regexp_replace 函數(shù)

語法：regexp_replace(str_source, pattern_str, rep_str)

功能：

? 支持正則表達(dá)式，用法類似于 replace，但功能更強(qiáng)大。

? 當(dāng)rep_str為空時(shí)，把str_source中的pattern_str字符串剔除。

select regexp_replace('abccc123','abc','*') as col from dual;
-- *cc123
select regexp_replace('abccc123','[ac]','*') as col from dual;
-- *b***123
select regexp_replace('abccc123','[ac]') as col from dual;
-- b123

可使用正則表達(dá)式進(jìn)行替換，[ac]表示a或者c字符。

3.translate 函數(shù)

語法：translate(char, from, to) -- 針對(duì)單個(gè)字符替換

功能：

? 將from替換為to字符串，逐個(gè)替換。

? 如果from比to字符串長，那么在from中比to中多出的字符串將會(huì)被刪除。

? 三個(gè)參數(shù)中有一個(gè)為空，返回為空。

select translate('abcdefga','abc','wo') as col from dual;
-- wodefgw
select translate('abcdefga','#abc','#') as col from dual;
-- defg

a替換為w，b替換為o，c刪除。

translate有#的特殊用法，以#開頭表示所有字符。

select translate('12334567','1233','abcd') as col from dual;
-- abcc4567

如果相同字符對(duì)應(yīng)多個(gè)字符，按第一個(gè)。

應(yīng)用1

在實(shí)際業(yè)務(wù)中，可以用來刪除一些異常數(shù)據(jù)，比如電話號(hào)碼的有效性，11位數(shù)字，可以使用 translate 函數(shù)進(jìn)行過濾。

-- 創(chuàng)建測(cè)試表
create table temp_cwh_001
(
 acc_nbr varchar2(20)
);
-- 插入數(shù)據(jù)
insert into temp_cwh_001 values('19912345678');
insert into temp_cwh_001 values('18812345678');
insert into temp_cwh_001 values('aaabbbcccdd');
-- 查詢
select * from temp_cwh_001;
-- 測(cè)試
select * from temp_cwh_001 a
where length(translate(trim(a.acc_nbr),
                       '0123456789' || a.acc_nbr,
                       '0123456789')) = length(trim(a.acc_nbr));
-- 18812345678
-- 19912345678

解釋：通過在acc_nbr字段的前面拼上0123456789數(shù)字字符串之后，將0123456789替換為0123456789，則其他非數(shù)字字符串會(huì)被替換為空，再通過判斷替換前后的字符串長度，則可之后該號(hào)碼是否有效。

其中，trim 函數(shù)用于去除指定字符的前后空格。

ltrim 函數(shù)用于去除指定字符的前面空格。

rtrim 函數(shù)用于去除指定字符的后面空格。

應(yīng)用2

如果想保留某些特定字符，篩選掉其他的。比如篩掉漢字保留數(shù)字，則可以：

先把數(shù)字篩選掉：

select translate('你我3阿斯蒂芬2的6撒謊8發(fā)', '#0123456789', '#') as col from dual;
-- 你我阿斯蒂芬的撒謊發(fā)

再用篩選出的漢字去篩選原來的語句留下數(shù)字：

select translate('你我3阿斯蒂芬2的6撒謊8發(fā)','#'||translate('你我3阿斯蒂芬2的6撒謊8發(fā)', '#0123456789', '#'),'#') as col from dual;
-- 3268

應(yīng)用3

如果有2個(gè)字符串都是數(shù)字字符，其中數(shù)字字符的順序不同，且每個(gè)字符只出現(xiàn)一次，通過靈活用法可判斷兩個(gè)字符串包含的數(shù)字是否完全一致。

select 1 from dual
where translate('0123456789','123','aaaaaa') = translate('0123456789','132','aaaaaa')
-- 1

where中的等式成立，包含數(shù)字一致。

參考鏈接1：oracle中的替換函數(shù)replace和translate函數(shù)

參考鏈接2：ORACLE中的字符串替換 replce、regexp_replace 和 translate

參考鏈接3：oracle 中translate的基本用法