CODEWITHSUNDEEP
regexpostgresql
Daniel
Daniel

Reputation: 1364

Postgres Regex Negative Lookahead

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.

Upvotes: 2

Views: 8479

Answers (2)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627262

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';

enter image description here

Upvotes: 4

klin
klin

Reputation: 121794

RE is a right side operand:

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

 ?column? 
----------
        1
(1 row)

Upvotes: 3

Related Questions