Purpose
REPLACE
returnschar
with every occurrence ofsearch_string
replaced withreplacement_string
. Ifreplacement_string
is omitted or null, then all occurrences ofsearch_string
are removed. Ifsearch_string
is null, thenchar
is returned.
Bothsearch_string
andreplacement_string
, as well aschar
, can be any of the data typesCHAR
,VARCHAR2
,NCHAR
,NVARCHAR2
,CLOB
, orNCLOB
. The string returned is in the same character set aschar
. The function returnsVARCHAR2
if the first argument is not a LOB and returnsCLOB
if the first argument is a LOB.
Examples
The following example replaces occurrences ofJ
withBL
:
SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL; Changes -------------- BLACK and BLUE
第二种方式,使用translate函数:
ChenZw> SELECT TRANSLATE('(1234,56789,12345)','(,)','''*''') AS STR FROM DUAL;
STR
------------------
'1234*56789*12345'
已选择 1 行。
使用这个函数处理这个问题就看起来比较舒服一点,至少没有replace那么暴力,看一下官方关于translate函数的介绍:
Purpose
TRANSLATE
returnsexpr
with all occurrences of each character infrom_string
replaced by its corresponding character into_string
. Characters inexpr
that are not infrom_string
are not replaced. The argumentfrom_string
can contain more characters thanto_string
. In this case, the extra characters at the end offrom_string
have no corresponding characters into_string
. If these extra characters appear inexpr
, then they are removed from the return value.
If a character appears multiple times infrom_string
, then theto_string
mapping corresponding to the first occurrence is used.
You cannot use an empty string forto_string
to remove all characters infrom_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 infrom_string
, concatenate another character to the beginning offrom_string
and specify this character as theto_string
. For example,TRANSLATE
(expr
, 'x0123456789
', 'x
') removes all digits fromexpr
.
TRANSLATE
provides functionality related to that provided by theREPLACE
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 supportCLOB
data directly. However,CLOB
s can be passed in as arguments through implicit data conversion.
Examples
The following statement translates a book title into a string that could be used (for example) as a filename. Thefrom_string
contains four characters: a space, asterisk, slash, and apostrophe (with an extra apostrophe as the escape character). Theto_string
contains only three underscores. This leaves the fourth character in thefrom_string
without a corresponding replacement, so apostrophes are dropped from the returned value.
SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL; TRANSLATE('SQL*PLUSU -------------------- SQL_Plus_Users_Guide