Oracle数据库SQL参考——REGEXP_REPLACE正则替换

REGEXP_REPLACE句法

目的

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions130.htm

regexp_replace函数是replace函数的扩展函数，用于通过正则表达式来进行匹配替换，默认情况下，函数返回source_char时，每次匹配到的正则，都替换为replace_string，返回的字符串与source_char字符集相同。如果source_char为非LOB类型，则返回varchar2数据类型，如果为LOB类型，则返回CLOB类型。

此函数符合POSIX正则和Unicode正则。

• source_char是一个用作搜索的值，它通常是一种字符列，可以以下是任何数据类型CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、NCLOB
• pattern是正则表达式，可以是以下任意数据类型CHAR、VARCHAR2、NCHAR、NVARCHAR2。最多可包含512个字节。如果pattern数据类型与source_char异同，则Oracle将转换pattern的数据类型与source_char的一致。
• replace_string可以是以下任意数据类型CHAR、VARCHAR2、NCHAR、NVARCHAR2、CLOB、NCLOB。如果replace_string是CLOB或NCLOB，则Oracle截断replace_string为32K。replace_string可含有多达500个反向引用作为子表达式，其形式为\n，n为数字1~9。如果n在replace_string中为反斜线字符，则需使用转移字符在其前面（\）
• position是一个正整数，表示在source_char中Oracle应该开始搜索的字符。默认值为1，表示Oracle以第一个字符开始搜索。
  • occurrence是一个非负整数，表示替换动作的发生：
  • 如果为0，则Oracle将替换所有匹配项，如果为正整数n，则Oracle将替换第n个匹配项。
• match_parameter是一个文本文件，用于更改匹配行为，仅影响匹配过程，并不影响replace_string。可以指定以下一个或多个值：
  • ‘i’指定不区分大小写的匹配
  • ‘c’区分大小写的匹配
  • ‘n’允许‘.’（句点，配置任意字符）匹配换行符，如果省略此此参数，则句点与换行符不匹配
  • ‘m’将源字符串视为多行。Oracle将源字符串中的^或$视为行首或行尾，如果省略此参数，Oracle将源字符串视为单行
  • ‘x’忽略空格字符。默认情况下，空白字符与自身匹配。

如果指定多个矛盾值，Oracle将使用最后一个值，例如指定’ic’，则Oracle使用区分大小写匹配，如果指定的不是上述字符，则返回错误
如果省略match_parameter，则：

• 默认的区分大小写由NLS_SORT参数的值决定
• 句点‘.’与换行符不匹配
• 源字符串视为单行

示例

SELECT
  REGEXP_REPLACE(phone_number,
                 '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
                 '(\1) \2-\3') "REGEXP_REPLACE"
  FROM employees;

REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4567
(515) 123-4568
(515) 123-4569
(590) 423-4567
. . .

SELECT
  REGEXP_REPLACE(country_name, '(.)', '\1 ') "REGEXP_REPLACE"
  FROM countries;

REGEXP_REPLACE
--------------------------------------------------------------------------------
A r g e n t i n a
A u s t r a l i a
B e l g i u m
B r a z i l
C a n a d a
. . .

SELECT
  REGEXP_REPLACE('500   Oracle     Parkway,    Redwood  Shores, CA',
                 '( ){2,}', ' ') "REGEXP_REPLACE"
  FROM DUAL;

REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA