Oracle replace替换多个,oracle 的两个替换函数 REPLACE TRANSLATE

replace就是一般意义上的字符串替换，translate只是一个字符替换对应的一个字符，下面有手册说明，还有例子 O(∩_∩)O哈哈~

REPLACE

Syntax

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.

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 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.

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.

Examples

The following example replaces occurrences of J with BL:

SELECT REPLACE('JACK and JUE','J','BL') "Changes"

from DUAL;

Changes

--------------

BLACK and BLUE

TRANSLATE

Syntax

PurposeTRANSLATE 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. If expr is a character string, then you must enclose it in single

quotation marks. 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 Database

interprets the empty string as null, and if this function has a null argument, then it returns null.

TRANSLATE provides functionality related to that provided by the REPLACE function