Postgres Regex Negative Lookahead

Question

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.

Just to be sure do you have any rows in your table? Because it does seem you wrote SELECT 1 WHERE TRUE, which should work as long as there are any rows — Xyzk
– Xyzk, Commented May 9, 2017 at 17:02
Right side operand...so obvious. I realize I don't need regex for my exact example but this is part of a much bigger picture. — Daniel
– Daniel, Commented May 9, 2017 at 20:34
@Xyzk There doesn't need to be a FROM clause or a table for this very simple example to work . It was simply used to test the regex — Daniel
– Daniel, Commented May 9, 2017 at 20:40

Wiktor Stribiżew · Accepted Answer · 2017-05-09 19:14:27Z

4

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

edited May 9, 2017 at 19:14

answered May 9, 2017 at 18:51

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Daniel Over a year ago

Marking as the answer since you've added the end of string piece '$' that I hadn't seen yet. My biggest problem was just that my silly test case had the operand sides swapped.

Wiktor Stribiżew Over a year ago

@Daniel: Yes, another improvement is that you actually do not need .* at the end as the regex check performed with ~ does not require a full string match (unlike LIKE or SIMILAR TO patterns that need a whole string match).

klin · Accepted Answer · 2017-05-09 17:45:12Z

3

RE is a right side operand:

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

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

edited May 9, 2017 at 17:45

answered May 9, 2017 at 17:37

klin

123k15 gold badges240 silver badges262 bronze badges

4 Comments

Wiktor Stribiżew Over a year ago

However, with this pattern, OP won't achieve the required results.

klin Over a year ago

@WiktorStribiżew - I do not know how OP is going to use it, but this works well.

Daniel Over a year ago

Really appreciate you pointing out the obvious flaw in my operand sides. That's what was mostly stumping me on getting this simple look ahead working before I put it into the more complex code.

Mikko Rantalainen Over a year ago

I think the original question was only missing the end anchor ($) from the negative lookahead.

Collectives™ on Stack Overflow

Postgres Regex Negative Lookahead

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related