http://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm
REGEXP_REPLACE
![Description of regexp_replace.gif follows Description of regexp_replace.gif follows](https://i-blog.csdnimg.cn/blog_migrate/94ec76554ec7b2d4fa9fea6f58683a55.gif)
Description of the illustration regexp_replace.gif
REGEXP_REPLACE
extends the functionality of the REPLACE
function by letting you search a string for a regular expression pattern. By default, the function returns source_char
with every occurrence of the regular expression pattern replaced with replace_string
. The string returned is in the same character set as source_char
. The function returns VARCHAR2
if the first argument is not a LOB and returns CLOB
if the first argument is a LOB.
This function complies with the POSIX regular expression standard and the Unicode Regular Expression Guidelines. For more information, please refer to Appendix C, "Oracle Regular Expression Support".
-
source_char
is a character expression that serves as the search value. It is commonly a character column and can be of any of the datatypesCHAR
,VARCHAR2
,NCHAR
,NVARCHAR2
,CLOB
orNCLOB
. -
pattern
is the regular expression. It is usually a text literal and can be of any of the datatypesCHAR
,VARCHAR2
,NCHAR
, orNVARCHAR2
. It can contain up to 512 bytes. If the datatype ofpattern
is different from the datatype ofsource_char
, Oracle Database convertspattern
to the datatype ofsource_char
. For a listing of the operators you can specify inpattern
, please refer to Appendix C, "Oracle Regular Expression Support". -
replace_string
can be of any of the datatypesCHAR
,VARCHAR2
,NCHAR
,NVARCHAR2
,CLOB
, orNCLOB
. Ifreplace_string
is aCLOB
orNCLOB
, then Oracle truncatesreplace_string
to 32K. Thereplace_string
can contain up to 500 backreferences to subexpressions in the form\n
, wheren
is a number from 1 to 9. Ifn
is the backslash character inreplace_string
, then you must precede it with the escape character (\\
). For more information on backreference expressions, please refer to the notes to "Oracle Regular Expression Support", Table C-1. -
position
is a positive integer indicating the character ofsource_char
where Oracle should begin the search. The default is 1, meaning that Oracle begins the search at the first character ofsource_char
. -
occurrence
is a nonnegative integer indicating the occurrence of the replace operation:-
If you specify 0, then Oracle replaces all occurrences of the match.
-
If you specify a positive integer
n
, then Oracle replaces then
th occurrence.
-
-
match_parameter
is a text literal that lets you change the default matching behavior of the function. This argument affects only the matching process and has no effect onreplace_string
. You can specify one or more of the following values formatch_parameter
:-
'i'
specifies case-insensitive matching. -
'c'
specifies case-sensitive matching. -
'n'
allows the period (.), which is the match-any-character character, to match the newline character. If you omit this parameter, the period does not match the newline character. -
'm'
treats the source string as multiple lines. Oracle interprets^
and$
as the start and end, respectively, of any line anywhere in the source string, rather than only at the start or end of the entire source string. If you omit this parameter, Oracle treats the source string as a single line. -
'x' ignores whitespace characters. By default, whitespace characters match themselves.
If you specify multiple contradictory values, Oracle uses the last value. For example, if you specify
'ic'
, then Oracle uses case-sensitive matching. If you specify a character other than those shown above, then Oracle returns an error.If you omit
match_parameter
, then:-
The default case sensitivity is determined by the value of the
NLS_SORT
parameter. -
A period (.) does not match the newline character.
-
The source string is treated as a single line.
See Also:
-
REGEXP_INSTR, REGEXP_SUBSTR, and REGEXP_LIKE Condition
------转发例子
全部测试数据
SQL> select * from test_reg_substr;
A
-----------------------------------
ABC123XYZ
ABC123XYZ456
<Name>Edward</Name>
替换数字
SQL> SELECT
2 REGEXP_REPLACE (a,'[0-9]+','QQQ') AS A
3 FROM
4 test_reg_substr;
A
-----------------------------------------------
ABCQQQXYZ
ABCQQQXYZQQQ
<Name>Edward</Name>
替换数字(从第一个字母开始匹配,替换第1个匹配项目)
SQL> SELECT
2 REGEXP_REPLACE (a,'[0-9]+','QQQ', 1, 1) AS A
3 FROM
4 test_reg_substr;
A
------------------------------------------------------
ABCQQQXYZ
ABCQQQXYZ456
<Name>Edward</Name>
替换数字(从第一个字母开始匹配,替换第2个匹配项目)
SQL> SELECT
2 REGEXP_REPLACE (a,'[0-9]+','Q', 1, 2) AS A
3 FROM
4 test_reg_substr;
A
---------------------------------------------------
ABC123XYZ
ABC123XYZQ
<Name>Edward</Name>
替换第二个单词
SQL> SELECT
2 REGEXP_REPLACE (a,'\w+','Kimi', 1, 2) AS A
3 FROM
4 test_reg_substr;
A
---------------------------------------------------
ABC123XYZ
ABC123XYZ456
<Name>Kimi</Name>
替换掉大写字母,小写字母,数字 0-9 为空
select regexp_replace(regexp_replace(regexp_replace(string_In,'[A-Z]',''),'[0-9]',''),'[a-z]','')
INTO V_RETURN from dual
-