[20240329]oracle replace与translate的区别.txt

[20240329]oracle replace与translate的区别.txt --//简单记录两者的区别: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/REPLACE.html REPLACE Syntax Description of replace.eps follows Description of the illustration replace.eps Purpose REPLACE returns char with every occurrence of search_string replaced with replacement_string. If replacement_string is omitted or null, then all occurrences of search_string are removed. If search_string is null, then char is returned. 将返回字符替换为每次将search_string替换为replacement_string。如果replacement_string忽略或null，那么所有出现的 search_stran被删除。如果search_string为null，则返回char。 Both search_string and replacement_string, as well as char, can be any of the data types CHAR, VARCHAR2, NCHAR, NVARCHAR2, CLOB, or NCLOB. The string returned is in the same character set as char. The function returns VARCHAR2 if the first argument is not a LOB and returns CLOB if the first argument is a LOB. search_strant和sepant_strang以及字符都可以是任何数据类型char、VARCHAR2、NCHAR、NVARCHAR2、CLOB或NCLOB。返回的字符串与 char中的字符集相同。如果第一个参数不是LOB，则该函数返回VARCHAR2，如果第一个参数是LOB，则该函数返回CLOB。 REPLACE provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings. 替换提供了与翻译功能所提供的功能相关的功能。翻译提供了单字符，一对一的替换。替换使您可以用一个字符串替换另一个字符串，并 删除字符串。 https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/TRANSLATE.html TRANSLATE Syntax Description of translate.eps follows Description of the illustration translate.eps Purpose TRANSLATE returns expr with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in expr that are not in from_string are not replaced. The argument from_string can contain more characters than to_string. In this case, the extra characters at the end of from_string have no corresponding characters in to_string. If these extra characters appear in expr, then they are removed from the return value. If a character appears multiple times in from_string, then the to_string mapping corresponding to the first occurrence is used. You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle Database interprets the empty string as null, and if this function has a null argument, then it returns null. To remove all characters in from_string, concatenate another character to the beginning of from_string and specify this character as the to_string. For example, TRANSLATE(expr, 'x0123456789', 'x') removes all digits from expr. TRANSLATE provides functionality related to that provided by the REPLACE function. REPLACE lets you substitute a single string for another single string, as well as remove character strings. TRANSLATE lets you make several single-character, one-to-one substitutions in one operation. This function does not support CLOB data directly. However, CLOBs can be passed in as arguments through implicit data conversion. 1.环境: SCOTT@book> @ver1 PORT_STRING                    VERSION        BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production 2.测试: SCOTT@book> select replace('11122','12','ab') from dual ; REPLA ----- 11ab2 SCOTT@book> select translate('11122','12','ab') from dual ; TRANS ----- aaabb --//replace是替换,测试例子是相当于12换成ab. --//translate相当于对应替换,1换成a,2换成b. SCOTT@book> select translate('11122','12','a') from dual ; TRA --- aaa --//相当于1替换a ,2 替换为null. --//今天看别人写的代码通过这个判断是否包含英文字符,类似上面的例子TRANSLATE(expr, 'x0123456789', 'x') removes all digits from expr. SCOTT@book> select translate('121a2b12b','x0123456789','x') from dual ; TRA --- abb --//我个人很少使用translate函数.有一点注意translate不直接支持CLOB类型,但是可以转换字符类型,也就是如果超过字符类型长度限制会报错. --//REPLACE函数没有这个问题,我常用来删除sql_fulltext的chr(13)字符.