CODEWITHSUNDEEP
regexpostgresql
gvgvgvijayan
gvgvgvijayan

Reputation: 2506

Regex working in regex engine but not in postgresql

I tried to match number 13 in pipe separated string like the one below:

13 - match
1|2|13 - match
13|1|2 - match
1|13|2 - match

1345|1|2 - should fail
1|1345|2 - should fail
1|2|1345 - should fail
1|4513|2 - should fail
4513|1|2 - should fail
2|3|4|4513- should fail

So, if 13 only occurs at the beginning or end, or in-between the string as a whole word it should match.

For that I wrote the following regex:

^13$|(\|13\|)?(?(1)|(^13\||\|13$))

In Regex101 it is working as expected. Please click link to see my sample.

But in Postgresql it throws error for the following query:

SELECT * FROM tbl_privilage WHERE user_id = 24 and show_id ~ '^13$|(\|13\|)?(?(1)|(^13\||\|13$))';

Error:

ERROR: invalid regular expression: quantifier operand invalid

SQL state: 2201B

Upvotes: 1

Views: 2456

Answers (3)

Wiktor Stribiżew
Wiktor Stribiżew

Reputation: 627537

You need to match 13 within word boundaries.

You need 

[[:<:]]13[[:>:]]

This solution should work even if you have spaces around the numeric values.

See documentation:

There are two special cases of bracket expressions: the bracket expressions [[:<:]] and [[:>:]] are constraints, matching empty strings at the beginning and end of a word respectively.

Upvotes: 1

user330315
user330315

Reputation:

Don't use a regex, using an array is more robust (and maybe more efficient as well):

select *
from the_table
where '13' = any (string_to_array(the_column, '|'));

this assumes that there is no whitespace between the values and the delimiter. You can even index that expression which probably makes searching a lot faster.

But I agree with Frank: you should really fix your data model.

Upvotes: 1

ArtemGr
ArtemGr

Reputation: 12567

Documentation is quite clear, saying that operator ~ implements the POSIX regular expressions. In Regex101 you're using PCRE (Perl-compatible) regular expressions. The two are very different.

If you need PCRE regular expressions in PostgreSQL you can setup an extension. Like pgpcre.

Upvotes: 2

Related Questions