oracle学习笔记:字符串替换 replace、regexp_replace、translate函数

1.replace 函数

语法:replace(char, search_string, replacement_string) --针对字符串替换

功能:

​ 将char中的字符串替换。

​ 当replacement_string为空时,剔除search_string。

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

完全匹配的字符才会进行替换,如无字符匹配,则返回原字符串。

该匹配是贪婪匹配,匹配都的字符串都会被替换,不仅仅是第一个匹配到的字符串。

2.regexp_replace 函数

语法:regexp_replace(str_source, pattern_str, rep_str)

功能:

​ 支持正则表达式,用法类似于 replace,但功能更强大。

​ 当rep_str为空时,把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

可使用正则表达式进行替换,[ac]表示a或者c字符。

3.translate 函数

语法:translate(char, from, to) -- 针对单个字符替换

功能:

​ 将from替换为to字符串,逐个替换。

​ 如果from比to字符串长,那么在from中比to中多出的字符串将会被删除。

​ 三个参数中有一个为空,返回为空。

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

如果相同字符对应多个字符,按第一个。

应用1

在实际业务中,可以用来删除一些异常数据,比如电话号码的有效性,11位数字,可以使用 translate 函数进行过滤。

-- 创建测试表
create table temp_cwh_001
(
 acc_nbr varchar2(20)
);
-- 插入数据
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;
-- 测试
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数字字符串之后,将0123456789替换为0123456789,则其他非数字字符串会被替换为空,再通过判断替换前后的字符串长度,则可之后该号码是否有效。

其中,trim 函数用于去除指定字符的前后空格。

ltrim 函数用于去除指定字符的前面空格。

rtrim 函数用于去除指定字符的后面空格。

应用2

如果想保留某些特定字符,筛选掉其他的。比如筛掉汉字保留数字,则可以:

先把数字筛选掉:

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

再用筛选出的汉字去筛选原来的语句留下数字:

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

应用3

如果有2个字符串都是数字字符,其中数字字符的顺序不同,且每个字符只出现一次,通过灵活用法可判断两个字符串包含的数字是否完全一致。

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

where中的等式成立,包含数字一致。

参考链接1:oracle中的替换函数replace和translate函数

参考链接2:ORACLE中的字符串替换 replce、regexp_replace 和 translate

参考链接3:oracle 中translate的基本用法

上一篇:oracle-常用函数


下一篇:delphi 格式转换