ORACLE replace和translate函数详解
前端之家收集整理的这篇文章主要介绍了
ORACLE replace和translate函数详解，
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
简要比较：
replace：字符串级别的代替
如：
SELECT
REPLACE(
'acdd
','
cd
ef
')
FROMdual; →aefd
translate：字符级别的代替
如：
TRANSLATE(
acdd
FROMdual; →aeff
分别详解：
replace:
语法：REPLACE ( char,search_string [,replacement_string] )
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.
解释：repalce中，每个search_string都被replacement_string所代替。
select
replace(
fromdual;→aefd
如果replacement_string为空或为NULL,那么所有的search_string都被移除。
'')
fromdual;→ad
如果search_string为null,那么就返回原来的char。
'',255);">fromdual;→acdd
fromdual;→acdd(也是两者都为空的情况)
Both search_string and replacement_string,as well as char,can be any of the datatypes CHAR,VARCHAR2,NCHAR,NVARCHAR2,CLOB,or NCLOB. The string returned is of VARCHAR2 datatype and is in the same character set as char.
解释：这段指明了可以用的数据类型.
This function 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.
解释：红色部分也是replace和translate的区别。
translate:
语法：TRANSLATE ( 'char','from_string','to_string' )
TRANSLATE returns char with all occurrences of each character in from_string replaced by its corresponding character in to_string. Characters in char 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 char,then they are removed from the return value.You cannot use an empty string for to_string to remove all characters in from_string from the return value. Oracle interprets the empty string as null,and if this function has a null argument,then it returns null.
解释：Translate中，每个from_string中的字符被to_string中相应的字符所代替。
selecttranslate(
fromdual;→aeff
