Oracle中的translate函数和replace函数的用法

3月 24, 2021

接触到此函数目的是为了判断某一单元格数据是在这里插入代码片否为纯数字，虽然实际来说TRANSLATE()实际不是为了判断数据格式。

translate函数语法：

1	translate(expr, from_string, to_string)

功能：在expr中寻找匹配from_string中符合的字符下标，随后根据下标去to_string中寻找对应的字符替换掉expr中对应下标的字符。

expr：进行处理匹配替换的数据

from_string：需要在expr中寻找匹配的字符

to_string：替换在from_string中找到的expr中对应下标的字符

栗子：

1 2	select translate('ac','ab2','00') from dual; --return 0c

首先拿出ac中的a去匹配ab2中的字符，他会一个字符一个字符的进行判断。

1
2
3

a==a true      	//a和ab2中的第一个字符比较，结果正确
a==b false      //a和ab2中的第二个字符比较，结果错误
a==2 false      //a和ab2中的第三个字符比较，结果错误

而其中的a匹配到了ab2中的字符，a在ab2中为第一位，所以也去00中寻找第一位进行替换，这时是0c。

再去匹配第二位的c，而在ab2中没有找到，所以不进行替换。

所以返回的结果为0c。

栗子ii：

1 2	select translate('ac1','abc123','abc') from dual; --return ac

当to_string比from_string短时，会对多余的位数进行剔除

这里中进行匹配到最后的1时，发现1在abc123中位置为第四位，而通过第四位去替换，去寻找abc的第四位时发现abc没有第四位，这时会将1剔除，返回的结果为ac。

需要注意的是，to_str不能是NULL或者’’，否则会返回空值。

translate也不能用于CLOB，再说得简单一点，也就是
如果string中的字符如果在from_string中没有，那么返回时被保留
如果string中的字符是from_string中的”额外字符”，那么返回时被删除
如果string中的字符在from_string中找到，且在to_string中有相应位置的字符，那么返回时用to_string中的字符替换string中的字符

replace函数

语法：

1	REPLACE(expr, from_string,to_string)

功能：用法和上文中的translate的用法相同，唯一不同的是translate是会对expr中的字符一个一个和from_string进行匹配，而replace却类似于模糊查询like，会在expr中查找包含from_string的部分并且进行替换为to_string。

expr：进行处理匹配替换的数据

from_string：需要在expr中寻找匹配的字符

to_string：替换在from_string中找到的expr中对应下标的字符

栗子：

select REPLACE('fgsgswsgs', 'fk' ,'j') 返回值 from dual;
--return  fgsgswsgs
select REPLACE('fgsgswsgs', 'sg' ,'eeerrrttt') 返回值 from dual;
--return  fgeeerrrtttsweeerrrttts

分析：

第一个例子中由于’fgsgswsgs’中没有与’fk’匹配的字符串，

故返回值仍然是’fgsgswsgs’；

第二个例子中将’fgsgswsgs’中的字符串’sg’全部转换为’eeerrrttt’。

总结：综上所述，replace与translate都是替代函数，

只不过replace针对的是字符串，而translate针对的是单个字符。

和replace函数的区别

select translate('itmyhome#163%com', '#%', '@.') from dual;
--return itmyhome@163.com

select replace('itmyhome#163%com', '#%', '@.') from dual;
--return  itmyhome#163%com

上面的translate函数是将#替换为@，%替换为.

而replace却没有实现此效果，是因为没有找到#%整体的组合

部分引自

戦いの前夜

sql study