Snowflake中REGEXP_SUBSTR使用后向查找(?<=pattern)模式报错咨询

问题根因 #

Snowflake SQL 内置的REGEXP_SUBSTR函数默认使用 POSIX ERE（扩展正则表达式）语法，该语法不支持后行断言（(?<=...)结构）。你之前在JavaScript存储过程中使用的是PCRE兼容的正则语法，天然支持后行断言，所以两边表现不一致。报错中提到的?识别异常，就是因为POSIX语法将后行断言开头的?识别为普通量词，找不到前面的匹配对象就触发了语法错误。

另外你原有正则还存在两个逻辑问题：

• [TABLE|VIEW|PROCEDURE]是字符组语法，只会匹配括号内的任意单个字符，不能匹配TABLE/VIEW/PROCEDURE三个完整单词，需要改为圆括号分组
• 后行断言后面多余的.会额外匹配一个不必要的字符，需要删除

适配方案 #

方案1：使用捕获组提取（兼容性最高，推荐） #

不需要修改正则解析规则，直接把待提取的对象名放到捕获组中，指定REGEXP_SUBSTR返回对应捕获组的结果即可：

REGEXP_SUBSTR(QUERY_TEXT, 'DROP\\s+(TABLE|VIEW|PROCEDURE)\\s+([A-Za-z0-9"_.]+)', 1, 1, 'im', 2)

这里最后一个参数2表示返回第二个捕获组的内容，也就是你需要的对象全路径，注意SQL字符串中转义反斜杠需要写为\\。

方案2：开启PCRE语法支持 #

如果需要保留后行断言的写法，可以在正则开头添加/*=pcre*/前缀，指定Snowflake使用PCRE语法解析正则：

REGEXP_SUBSTR(QUERY_TEXT, '/*=pcre*/(?<=DROP\\s+(TABLE|VIEW|PROCEDURE)\\s+)[A-Za-z0-9"_.]+', 1, 1, 'im')

内容的提问来源于stack exchange，提问作者akshindesnowflake