§ Oracle兼容-函数-REGEXP_REPLACE()函数


§ 1. 语法

REGEXP_REPLACE ( source_char, pattern
                 [, replace_string
                    [, position
                       [, occurrence
                          [, match_param ]
                       ]
                    ]
                 ]
               )
1
2
3
4
5
6
7
8
9

§ 2. 定义和用法

REGEXP_REPLACE() 函数是 REPLACE() 函数的扩展函数,用于通过正则表达式来进行匹配替换。默认情况下,该函数返回 source_char 并将每次出现的正则表达式模式替换为 replace_string,返回的字符串与 source_char 的字符集相同。

各参数说明如下:

  • source_char 是源字符串,是用作搜索匹配的字符表达式。

    • 指定 NULL 值时,结果返回 NULL。
    • 指定空字符串时,结果返回空字符串。
  • pattern 是正则表达式模式。遵循 Unicode 正则表达式指南,有关详细信息,请阅读 ICU’s Regular Expressions (opens new window)

    • 指定 NULL 值时,结果返回源字符串。
    • 指定空字符串或无效的正则表达式时,结果返回错误。
  • replace_string 是用于替换在源字符串中由正则表达式匹配的字符串,可以包含 \n 形式的子表达式的反向引用,其中 n 是从 1 到 9 的数字。如果要包含反斜杠 \,则必须在它前面加上转义字符,它也是一个反斜杠 \。例如,要替换 \2,可以输入 \\2

    • 指定 NULL 值或空字符串时,将在源字符串中由正则表达式匹配的字符串替换为空字符串。
  • position 是一个正整数,表示应该从源字符串的哪个字符开始搜索匹配,当大于源字符串长度时结果返回源字符串,默认值为 1。

  • occurrence 是一个非负整数,表示替换的匹配项:

    • 如果指定 0,则将替换所有出现的匹配项。
    • 如果指定一个正整数 n,那么将替换第 n 个出现的位置。如果 occurrence 大于 1,则从 pattern 第一次出现后的第一个字符开始搜索第二次出现,依此类推。
  • match_param 指定如何执行匹配的字符串。

    • 'i' 不区分大小写的匹配。

    • 'c' 区分大小写匹配。

    • 'n' 允许 . 匹配换行符,如果省略此参数,则与换行符不匹配。

    • 'm' 将源字符串视为多行。将 ^ 和 $ 分别解释为源字符串中任意行的开始和结束,而不仅仅是整个源字符串的开始或结束。如果省略此参数,则将源字符串视为单行。

    • 'x' 忽略正则表达式模式中的空格。

    • 如果在 match_param 中指定了指定矛盾选项的字符,则最右边的优先。

    • 如果省略 match_param,则:

      • 默认大小写敏感性由 REGEXP_REPLACE() 函数的排序规则确定。
      • . 与换行符不匹配。
      • 源字符串被视为单行。

§ 3. Oracle兼容说明

  1. Oracle的正则表达式中数量限定元字符前面可以为空,而GreatSQL中不支持。
REGEXP_REPLACE 输入 Oracle GreatSQL
REGEXP_REPLACE('123abc', '*', 'xxx') xxx1xxx2xxx3xxxaxxxbxxxcxxx ERROR 3688 (HY000): Syntax error in regular expression on line 1, character 1.
REGEXP_REPLACE('123abc', '+', 'xxx') xxx1xxx2xxx3xxxaxxxbxxxcxxx ERROR 3688 (HY000): Syntax error in regular expression on line 1, character 1.
REGEXP_REPLACE('123abc', '?', 'xxx') xxx1xxx2xxx3xxxaxxxbxxxcxxx ERROR 3688 (HY000): Syntax error in regular expression on line 1, character 1.
REGEXP_REPLACE('123abc', '{2}', 'xxx') xxx1xxx2xxx3xxxaxxxbxxxcxxx ERROR 3688 (HY000): Syntax error in regular expression on line 1, character 1.
  1. Oracle的正则表达式中支持非法花括号表达式,而GreatSQL中不支持。
REGEXP_REPLACE 输入 Oracle GreatSQL
REGEXP_REPLACE('123abc', '{', 'xxx') 123abc ERROR 3688 (HY000): Syntax error in regular expression on line 1, character 1.
REGEXP_REPLACE('123abc', '}', 'xxx') 123abc ERROR 3688 (HY000): Syntax error in regular expression on line 1, character 1.
REGEXP_REPLACE('123abc', '{}', 'xxx') 123abc ERROR 3688 (HY000): Syntax error in regular expression on line 1, character 1.
  1. Oracle的正则表达式允许空串的情况,而GreatSQL不允许
REGEXP_REPLACE 输入 Oracle GreatSQL
REGEXP_REPLACE('123abc', ' ', 'xxx', 1, 0, 'x') xxx1xxx2xxx3xxxaxxxbxxxcxxx ERROR 3685 (HY000): Illegal argument to a regular expression.
REGEXP_REPLACE('123abc', '', 'xxx', 1, 0, 'x') xxx1xxx2xxx3xxxaxxxbxxxcxxx ERROR 3689 (HY000): Unrecognized escape sequence in regular expression.

其他注意事项

  1. 在Oracle中没有空串,所以在GreatSQL中处理空串时如果想要得到与Oracle一样的结果,则需要修改 sql_mode 设置 SET sql_mode = EMPTYSTRING_EQUAL_NULL

  2. 在Oracle中反斜线 \ 不表示转义字符,所以在GreatSQL中处理反斜线 \ 时如果想要得到与Oracle一样的结果,则需要修改 sql_mode 设置 SET sql_mode = NO_BACKSLASH_ESCAPES

  3. 最后一个参数如果不指定是否区分大小写,则默认的大小写敏感性由 REGEXP_REPLACE() 函数的排序规则(即第一个参数的排序规则)确定,所以在大小写结果上可能与Oracle有差异。

例如:假设 REGEXP_REPLACE() 函数在Oracle的排序规则为 BINARY,在 GreatSQL 的排序规则为 utf8mb4_vi_0900_ai_ci

REGEXP_REPLACE 输入 Oracle GreatSQL
REGEXP_REPLACE('A', 'a', 'G') A G

§ 4. 示例

greatsql> SET sql_mode = 'ORACLE,EMPTYSTRING_EQUAL_NULL,NO_BACKSLASH_ESCAPES';

greatsql> SELECT REGEXP_REPLACE('111.222.3333', '([[:digit:]]{3})\.([[:digit:]]{3})\.([[:digit:]]{4})', '(\1) \2-\3') AS REGEXP_REPLACE FROM DUAL;
+----------------+
| REGEXP_REPLACE |
+----------------+
| (1) 2-3        |
+----------------+

greatsql> CREATE TABLE t_regexp (empName varchar2(20), emailID varchar2(20));
greatsql> INSERT INTO t_regexp (empName, emailID) VALUES ('John Doe', 'johndoe@example.com'), ('Jane Doe', 'janedoe@example.com');

greatsql> SELECT * FROM t_regexp;
+----------+---------------------+
| empName  | emailID             |
+----------+---------------------+
| John Doe | johndoe@example.com |
| Jane Doe | janedoe@example.com |
+----------+---------------------+

greatsql> SELECT empName, REGEXP_REPLACE (empName, 'Jane', 'John') AS REGEXP_REPLACE FROM t_regexp;
+----------+----------------+
| empName  | REGEXP_REPLACE |
+----------+----------------+
| John Doe | John Doe       |
| Jane Doe | John Doe       |
+----------+----------------+

greatsql> SELECT empName, REGEXP_REPLACE (empName, 'John', 'Jane') AS REGEXP_REPLACE  FROM t_regexp;
+----------+----------------+
| empName  | REGEXP_REPLACE |
+----------+----------------+
| John Doe | Jane Doe       |
| Jane Doe | Jane Doe       |
+----------+----------------+
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35

greatsql-wx