Postgres Regex负前瞻

本文介绍了Postgres Regex负前瞻的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

场景：匹配所有以 J01开头的字符串，除了字符串 J01FA09。

我很困惑为什么以下代码什么都不返回：

选择1 WHERE '^ J01（？！FA09）。*'〜'J01FA10'

当我可以在

Scenario: Match any string that starts with "J01" except the string "J01FA09".

I'm baffled why the following code returns nothing:

SELECT 1 WHERE '^J01(?!FA09).*' ~ 'J01FA10'

when I can see on regexr that it's working (I realize there are different flavors of regex and that could be the reason for the site working).

I have confirmed in the postgres documentation that negative look aheads are supported though.

Table 9-15. Regular Expression Constraints

(?!re) negative lookahead matches at any point where no substring matching re begins (AREs only). Lookahead constraints cannot contain back references (see Section 9.7.3.3), and all parentheses within them are considered non-capturing.

解决方案

Match any string that starts with "J01" except the string "J01FA09".

You can do without a regex using

WHERE s LIKE 'J01%' AND s != 'J01FA09'

Here, LIKE 'J01%' requires a string to start with J01 and then may have any chars after, and s != 'J01FA09' will filter out the matches.

If you want to ahieve the same with a regex, use

WHERE s ~ '^J01(?!FA09$)'

The ^ matches the start of a string, J01 matches the literal J01 substring and (?!FA09$) asserts that right after J01 there is no FA09 followed with the end of string position. IF the FA09 appears and there is end of string after it, no match will be returned.

See the online demo:

CREATE TABLE table1 (s character varying) ; INSERT INTO table1 (s) VALUES ('J01NNN'), ('J01FFF'), ('J01FA09'), ('J02FA09') ; SELECT * FROM table1 WHERE s ~ '^J01(?!FA09$)'; SELECT * FROM table1 WHERE s LIKE 'J01%' AND s != 'J01FA09';