场景:匹配所有以 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';更多推荐
Postgres Regex负前瞻
发布评论