Skip to content

正则表达式函数

BodoSQL 目前通过 re 模块使用 Python 的正则表达式库。虽然这可能会发生变化，但这意味着与 Snowflake 的正则表达式函数的行为存在几个偏差 (请参阅此处获取 Snowflake 文档)。关键点和主要偏差如下所示：

  • Snowflake 使用 POSIX ERE 正则表达式语法的超集。这意味着 BodoSQL 可以利用一些 Snowflake 不能使用的正则表达式的语法形式 (见此处以获取 Python re 文档)。然而，POSIX ERE 具有一些 Python 的 re 所不具备的特性：

  • POSIX字符类 (请参阅此处以获取完整列表)。BodoSQL确实将这些作为字符集的宏支持。换句话说， [[:lower:]] 被转换为 [a-z]。然而，这种替换形式无法转义。此外，任何应该包含空终止符 \x00 的字符类实际上是从 \x01 开始的。

  • 等价类（BodoSQL不支持）。

  • 当使用交替模式时返回最长的匹配（BodoSQL 返回最左侧的匹配）。

  • 正则表达式函数可以选择性地接受一个标志参数。标志是一个字符串，其中的字符控制模式匹配的方式。当字符包含在标志字符串中时，以下字符具有意义：

  • 'c': 区分大小写的匹配（默认行为）

  • 'i': 不区分大小写的匹配（如果同时提供 'c' 和 'i'，则使用最后一个出现的）
  • 'm': 允许锚点模式与每行的开始/结束互动，而不仅仅是整个字符串的开始/结束。
  • 's': 允许.元字符捕获换行符

  • 'e': 请参见 REGEXP_SUBSTR/REGEXP_INSTR

  • 目前，BodoSQL 支持惰性 ? 操作符，而 Snowflake 不支持。因此，例如，在 Snowflake 中，模式 `(.*?),' 将匹配尽可能多的字符，只要最后一个字符是逗号。然而，在 BodoSQL 中，匹配将在第一个逗号处结束。

  • 目前，BodoSQL 支持以下正则表达式特性，这些特性在 Snowflake 中使用时会崩溃： (?...), \A, \Z, \1, \2, \3 等。

  • 目前，BodoSQL要求模式参数和标志参数（如果提供）必须是字符串字面量，而不是列或表达式。

  • 目前，如果某些字符在Python中有特定含义，则可能需要额外的反斜杠来进行转义。正确定义转义字符所需的反斜杠数量取决于使用情况。

  • 所有匹配都是不重叠的。

  • 如果任何数字参数为零或负数，或者 group_num 参数超出范围，则会引发错误。唯一的例外是 REGEXP_REPLACE，它允许其出现参数为零。

BodoSQL 目前支持以下正则表达式函数：

REGEXP_LIKE

  • REGEXP_LIKE(str, pattern[, flag])

    如果整个字符串与模式匹配，则返回 true。
    如果未提供 flag，将使用 ''。

    如果模式是空的，则如果字符串也是空的，返回true。

    例如：

    • 2 个参数：如果 A 是一个 5 个字符的字符串，其中第一个字符是 a，最后一个字符是 z，中间的 3 个字符也是小写字母（区分大小写），则返回 true。 

      SELECT REGEXP_LIKE(A, 'a[a-z]{3}z')

    • 3 个参数：如果 A 以字母 'THE' 开头（不区分大小写），则返回 true。 

      SELECT REGEXP_LIKE(A, 'THE.*', 'i')

正则表达式计数

  • REGEXP_COUNT(str, pattern[, position[, flag]])

    返回字符串中与模式匹配的次数，从由 position 参数指定的位置开始（使用1索引）。如果没有提供 position，则使用 1。如果没有提供 flag，则使用 ''。

    如果模式为空，返回0。

    例如：

    • 2 个参数：返回任何字母在 A 中出现的次数。

    • 3个参数：返回任意数字字符在A中出现的次数，不包括前5个字符。

    • 4 个参数：返回子字符串在 A 中出现的次数，该子字符串包含两个 一和任意字符（包括换行符）在中间。 

      SELECT REGEXP_COUNT(A, '1.1', 1, 's')

正则表达式替换

  • REGEXP_REPLACE(str, pattern[, replacement[, position[, occurrence[, flag]]]])

    返回输入字符串的版本，其中每个与模式匹配的部分都被替换为替换字符串，从position参数指定的位置开始（使用1索引）。occurrence参数指定要替换的匹配项，其中0表示替换所有匹配项。如果未提供replacement，则使用''。如果未提供position，则使用1。如果未提供occurrence，则使用0。如果未提供flag，则使用''。

    如果匹配的数量不足，或者模式为空，原始字符串将被返回。

    注意

    替换模式中的反向引用是支持的，但可能需要额外的反斜杠才能正确工作。

    例如：

    • 2 个参数：删除所有在 A 中的空白字符。

    • 3 个参数：用 'love' 替换 A 中所有出现的 'hate'（区分大小写）。

      SELECT REGEXP_REPLACE(A, 'hate', 'love')

    • 4 个参数：将 A 中所有连续两个数字的出现替换为相同的两个数字的反转，不包括前两个字符。

    • 5 个参数：用下划线替换 A 中的第一个字符。

      SELECT REGEXP_REPLACE(A, '^.', '_', 1, 2)

    • 6 个参数：从每行中移除第一个和最后一个单词，这些行包含至少 3 个单词。 

      SELECT REGEXP_REPLACE(A, '^\w+ (.*) \w+$', '\\\\1', 0, 1, 'm')

正则表达式_SUBSTR

  • REGEXP_SUBSTR(str, pattern[, position[, occurrence[, flag[, group_num]]]])

    返回与模式匹配的原始字符串的子串，从由position参数指定的位置开始（使用1索引）。occurrence参数指定要提取哪个匹配（使用1索引）。如果未提供position，则使用1。如果未提供occurrence，则使用1。如果未提供flag，则使用''。如果未提供group_num，并且flag包含'e'，则使用1。如果提供了group_num但标志不包含e，则它的行为就像包含e。如果标志确实包含e，那么将返回匹配的一个子组，而不是整个匹配。返回的子组对应于group_num参数（使用1索引）。

    如果匹配的数量不足，或者模式为空，NULL 将被返回。

    例如：

    • 2 个参数：返回出现在 A 内部的第一个数字。

    • 3 个参数：返回在 A 中出现的第一个标点符号，排除前 10 个字符。

    • 4 个参数：返回字符串 A 中连续出现的第二个小写元音的第四个实例。 

      SELECT REGEXP_SUBSTR(A, '[aeiou]{2}', 1, 4)

    • 5 个参数：返回 A 中以元音字母开头和结尾的第一个 3+ 个字符的子字符串（不区分大小写，并且可以包含换行符）。

      SELECT REGEXP_SUBSTR(A, '[aeiou].+[aeiou]', 1, 1, 'im')

    • 6 个参数：查找 A 中第三次出现的一个数字，后面跟着一个冒号、一个空格和一个以 'a' 开头的单词（区分大小写），并返回以 'a' 开头的单词。 

      SELECT REGEXP_SUBSTR(A, '(\d+): (a\w+)', 1, 3, 'e', 2)

REGEXP_INSTR

  • REGEXP_INSTR(str, pattern[, position[, occurrence[, option[, flag[, group_num]]]]])

    返回原始字符串中导致与模式匹配的位置，从position参数指定的位置开始（使用1索引）。occurrence参数指定要提取的匹配项（使用1索引）。option参数指定是返回匹配的起始位置（如果0）还是匹配结束后的第一个位置（如果1）。如果未提供position，则使用1。如果未提供occurrence，则使用1。如果未提供option，则使用0。如果未提供flag，则使用''。如果未提供group_num，并且flag包含'e'，则使用1。如果提供了group_num，但标志不包含e，那么它的行为就像包含了一样。如果标志确实包含e，则返回匹配的子组之一的位置，而不是整个匹配的位置。返回的子组对应于group_num参数（使用1索引）。

    如果匹配的数量不足，或者模式为空，0 会被返回。

    • 2 个参数：返回 A 中第一个 '#' 的索引。

    • 3 个参数：返回在 A 中首次出现的 3 个连续数字的起始索引，排除前 3 个字符。 ```sql SELECT REGEXP_INSTR(A, '\d{3}', 4) 

      - 4 个参数：返回在 `A` 中夹在尖括号之间的第 9 个单词的起始索引。
```sql
SELECT REGEXP_INSTR(A, '<\w+>', 1, 9)

    • 5 个参数：返回第一个以非 ASCII 字符开头和结尾的子字符串的结束索引。 

      SELECT REGEXP_INSTR(A, '[^[:ascii:]].*[^[:ascii:]]', 1, 1, 1)

    • 6 个参数：返回以大写元音开头的 A 的第二行的起始索引。 

      SELECT REGEXP_INSTR(A, '^[AEIOU].*', 1, 2, 0, 'm')

    • 7 个参数：查找 A 中第一个符合电话簿名称格式的子字符串（即 Lastname, Firstname），并返回第一个名称的起始索引。 

      SELECT REGEXP_INSTR(A, '([[:upper]][[:lower:]]+), ([[:upper]][[:lower:]]+)', 1, 1, 0, 'e', 2)