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.


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.


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.


SCOTT@PROD2> select replace('abcabbaccbcc','a') from dual;  --如果省略replacement_string参数则移除所有search_string.


REPLACE('
---------

bcbbccbcc

--实验
--更新表将JOB结尾为MAN的为MEN。
SCOTT@PROD2> select * from t;

ENAME	   JOB
---------- ---------
SMITH	   CLERK
ALLEN	   SALESMAN
WARD	   SALESMAN
JONES	   MANAGER
MARTIN	   SALESMAN
BLAKE	   MANAGER
CLARK	   MANAGER
SCOTT	   ANALYST
KING	   PRESIDENT
TURNER	   SALESMAN
ADAMS	   CLERK
JAMES	   CLERK
FORD	   ANALYST
MILLER	   CLERK

14 rows selected.

SCOTT@PROD2> update t set job=replace(job,'MAN','MEN') where job = 'SALESMAN';

4 rows updated.

SCOTT@PROD2> select * from t;

ENAME	   JOB
---------- ---------
SMITH	   CLERK
ALLEN	   SALESMEN
WARD	   SALESMEN
JONES	   MANAGER
MARTIN	   SALESMEN
BLAKE	   MANAGER
CLARK	   MANAGER
SCOTT	   ANALYST
KING	   PRESIDENT
TURNER	   SALESMEN
ADAMS	   CLERK
JAMES	   CLERK
FORD	   ANALYST
MILLER	   CLERK

14 rows selected.