REGEXP_REPLACE
通过让您在字符串中搜索正则表达式模式
REGEXP_REPLACE
扩展
REPLACE
函数的功能。默认情况下，该函数返回
source_char
，每次出现的正则表达式模式都替换为
replace_string
。返回的字符串与
source_char
位于相同的字符集中。如果第一个参数不是LOB，则函数返回
VARCHAR2
如果第一个参数是LOB，则返回
CLOB
。
此函数符合POSIX正则表达式标准和Unicode正则表达式指南。有关更多信息，请参阅
Oracle正则表达式支持
。
source_char
是用作搜索值的字符表达式。它通常是字符列，可以是任何数据类型
CHAR
，
VARCHAR2
，
NCHAR
，
NVARCHAR2
，
CLOB
或
NCLOB
。
pattern
是正则表达式。它通常是文本文字，可以是任何数据类型
CHAR
，
VARCHAR2
，
NCHAR
或
NVARCHAR2
。它最多可包含512个字节。如果
pattern
的数据类型与
source_char
的数据类型不同，则Oracle数据库将
pattern
转换为
source_char
的数据类型。有关可以在
pattern
指定的运算符的列表，请参阅
Oracle正则表达式支持
。
replace_string
可以是任何数据类型
CHAR
，
VARCHAR2
，
NCHAR
，
NVARCHAR2
，
CLOB
或
NCLOB
。如果
replace_string
是
CLOB
或
NCLOB
，则Oracle
replace_string
截断为32K。
replace_string
最多可包含500个子表达式的反向引用，形式为
\n
，其中
n
是1到9之间的数字。如果要在
replace_string
包含反斜杠（
\
），则必须在其前面加上转义字符，也就是反斜杠。例如，要替换
\2
您将输入
\\2
。有关反向引用表达式的更多信息，请参阅
“
Oracle正则表达式支持
”的说明
，
表D-1
。
position
是一个正整数，表示Oracle应该开始搜索的
source_char
的字符。默认值为1，表示Oracle在
source_char
的第一个字符处开始搜索。
occurrence
是一个非负整数，表示替换操作的发生：
如果指定0，则Oracle将替换所有匹配项。
如果指定正整数
n
，则Oracle将替换第
n
个匹配项。
如果
occurrence
大于1，则数据库将搜索第二次出现，从第一次出现
pattern
后的第一个字符开始，依此类推。此行为与
INSTR
函数不同，
INSTR
函数开始在第一次出现的第二个字符处搜索第二个匹配项。
match_parameter
是一个文本文字，可让您更改函数的默认匹配行为。此参数的行为与此函数的行为与
REGEXP_COUNT
。有关详细信息，请参阅
REGEXP_COUNT
。
以下示例检查
phone_number
，查找模式
xxx
。
xxx
。
xxxx
。Oracle使用（
xxx
）
xxx
-
xxxx
重新格式化此模式。
SELECT
REGEXP_REPLACE(phone_number,
'([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})',
'(\1) \2-\3') "REGEXP_REPLACE"
FROM employees
ORDER BY "REGEXP_REPLACE";
REGEXP_REPLACE
--------------------------------------------------------------------------------
(515) 123-4444
(515) 123-4567
(515) 123-4568
(515) 123-4569
(515) 123-5555
. . .
以下示例检查
country_name
。Oracle在字符串中的每个非空字符后面放置一个空格。
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
. . .
以下示例检查字符串，查找两个或多个空格。Oracle用一个空格替换每个出现的两个或多个空格。
SELECT
REGEXP_REPLACE('500 Oracle Parkway, Redwood Shores, CA',
'( ){2,}', ' ') "REGEXP_REPLACE"
FROM DUAL;
REGEXP_REPLACE
--------------------------------------
500 Oracle Parkway, Redwood Shores, CA
REGEXP_REPLACE模式匹配：示例
以下语句创建表regexp_temp并将值插入其中：
CREATE TABLE regexp_temp(empName varchar2(20), emailID varchar2(20));
INSERT INTO regexp_temp (empName, emailID) VALUES ('John Doe', '[email protected]');
INSERT INTO regexp_temp (empName, emailID) VALUES ('Jane Doe', '[email protected]');
以下语句将字符串'Jane'替换为'John'：
SELECT empName, REGEXP_REPLACE (empName, 'Jane', 'John') "STRING_REPLACE" FROM regexp_temp;
EMPNAME STRING_REPLACE
-------- --------------
John Doe John Doe
Jane Doe John Doe
以下语句将字符串'John'替换为'Jane'：
SELECT empName, REGEXP_REPLACE (empName, 'Jane', 'John') "STRING_REPLACE" FROM regexp_temp;
EMPNAME STRING_REPLACE
-------- --------------
John Doe Jane Doe
Jane Doe Jane Doe
实时SQL：
在
REGEXP_REPLACE
上查看并运行Oracle Live SQL上的相关示例
- 模式匹配
REGEXP_REPLACE：示例
以下语句替换字符串中的所有数字：
WITH strings AS (
SELECT 'abc123' s FROM dual union all
SELECT '123abc' s FROM dual union all
SELECT 'a1b2c3' s FROM dual
SELECT s "STRING", regexp_replace(s, '[0-9]', '') "MODIFIED_STRING"
FROM strings;
STRING MODIFIED_STRING
-------------------- --------------------
abc123 abc
123abc abc
a1b2c3 abc
以下语句替换字符串中的第一个数字出现：
WITH strings AS (
SELECT 'abc123' s from DUAL union all
SELECT '123abc' s from DUAL union all
SELECT 'a1b2c3' s from DUAL
SELECT s "STRING", REGEXP_REPLACE(s, '[0-9]', '', 1, 1) "MODIFIED_STRING"
FROM strings;
STRING MODIFIED_STRING
-------------------- --------------------
abc123 abc23
123abc 23abc
a1b2c3 ab2c3
以下语句替换字符串中的第二个数字：
WITH strings AS (
SELECT 'abc123' s from DUAL union all
SELECT '123abc' s from DUAL union all
SELECT 'a1b2c3' s from DUAL
SELECT s "STRING", REGEXP_REPLACE(s, '[0-9]', '', 1, 2) "MODIFIED_STRING"
FROM strings;
STRING MODIFIED_STRING
-------------------- --------------------
abc123 abc13
123abc 13abc
a1b2c3 a1bc3
以下语句使用单个空格替换字符串中的多个空格：
WITH strings AS (
SELECT 'Hello World' s FROM dual union all
SELECT 'Hello World' s FROM dual union all
SELECT 'Hello, World !' s FROM dual
SELECT s "STRING", regexp_replace(s, ' {2,}', ' ') "MODIFIED_STRING"
FROM strings;
STRING MODIFIED_STRING
-------------------- --------------------
Hello World Hello World
Hello World Hello World
Hello, World ! Hello, World !
以下语句将驼峰大小写字符串转换为包含由下划线分隔的小写单词的字符串：
WITH strings as (
SELECT 'AddressLine1' s FROM dual union all
SELECT 'ZipCode' s FROM dual union all
SELECT 'Country' s FROM dual
SELECT s "STRING",
lower(regexp_replace(s, '([A-Z0-9])', '_\1', 2)) "MODIFIED_STRING"
FROM strings;
STRING MODIFIED_STRING
-------------------- --------------------
AddressLine1 address_line_1
ZipCode zip_code
Country country
以下语句转换日期的格式：
WITH date_strings AS (
SELECT '2015-01-01' d from dual union all
SELECT '2000-12-31' d from dual union all
SELECT '900-01-01' d from dual
SELECT d "STRING",
regexp_replace(d, '([[:digit:]]+)-([[:digit:]]{2})-([[:digit:]]{2})', '\3.\2.\1') "MODIFIED_STRING"
FROM date_strings;
STRING MODIFIED_STRING
-------------------- --------------------
2015-01-01 01.01.2015
2000-12-31 31.12.2000
900-01-01 01.01.900
以下语句将字符串中的所有字母替换为“1”：
WITH strings as (
SELECT 'NEW YORK' s FROM dual union all
SELECT 'New York' s FROM dual union all
SELECT 'new york' s FROM dual
SELECT s "STRING",
regexp_replace(s, '[a-z]', '1', 1, 0, 'i') "CASE_INSENSITIVE",
regexp_replace(s, '[a-z]', '1', 1, 0, 'c') "CASE_SENSITIVE",
regexp_replace(s, '[a-zA-Z]', '1', 1, 0, 'c') "CASE_SENSITIVE_MATCHING"
FROM strings;
STRING CASE_INSEN CASE_SENSI CASE_SENSI
---------- ---------- ---------- ----------
NEW YORK 111 1111 NEW YORK 111 1111
New York 111 1111 N11 Y111 111 1111
new york 111 1111 111 1111 111 1111
