Postgres Regex负前瞻 [英] Postgres Regex Negative Lookahead

问题描述

场景：匹配所有以 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.com 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';

这篇关于Postgres Regex负前瞻的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！