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

1. 语法

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

2. 定义和用法

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

各参数说明如下：

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

  • 指定 NULL 值时，结果返回 NULL。

  • 指定空字符串时，结果返回空字符串。

  • pattern 是正则表达式模式。遵循 Unicode 正则表达式指南，有关详细信息，请阅读 ICU’s Regular ExpressionsREGEXP_REPLACE - 图1 (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. 示例

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

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

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

13. greatsql> SELECT * FROM t_regexp;
14. +----------+---------------------+
15. | empName  | emailID             |
16. +----------+---------------------+
17. | John Doe | johndoe@example.com |
18. | Jane Doe | janedoe@example.com |
19. +----------+---------------------+

21. greatsql> SELECT empName, REGEXP_REPLACE (empName, 'Jane', 'John') AS REGEXP_REPLACE FROM t_regexp;
22. +----------+----------------+
23. | empName  | REGEXP_REPLACE |
24. +----------+----------------+
25. | John Doe | John Doe       |
26. | Jane Doe | John Doe       |
27. +----------+----------------+

29. greatsql> SELECT empName, REGEXP_REPLACE (empName, 'John', 'Jane') AS REGEXP_REPLACE  FROM t_regexp;
30. +----------+----------------+
31. | empName  | REGEXP_REPLACE |
32. +----------+----------------+
33. | John Doe | Jane Doe       |
34. | Jane Doe | Jane Doe       |
35. +----------+----------------+

问题反馈

联系我们

扫码关注微信公众号

greatsql-wx