1

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

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
gvgvgvijayan
  • 1,851
  • 18
  • 34
  • 3
    "pipe separated string ", why do you store your data like this? This creates a huge amount of problems, your regex is just one of many problems. Try to fix the real problem, your datamodel, instead of a bug fix for a bug fix. – Frank Heikens May 14 '15 at 11:00
  • @FrankHeikens I already asked about why not we followed normalization but they forced me to check it through regex we should not change schema design like that concern people replied me and I don't have rights too :( – gvgvgvijayan May 14 '15 at 11:03
  • 1
    Use at least an ARRAY if you don't/can't normalize. Still a workaround, but much better than a piece of problems. – Frank Heikens May 14 '15 at 11:05
  • @FrankHeikens thanks for the advice since I don't have DB access to production environment I already suggested higher official to use any collection object like as you said array but they said already we developed lots of php code depends on this pipe separated value so no way change that. You just try to achieve this task using pattern matching so I tried it using regex. But I like to use a_horse_with_no_name answer. Again thanks for your feed back. – gvgvgvijayan May 14 '15 at 11:19

3 Answers3

2

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.

Community
  • 1
  • 1
ArtemGr
  • 11,684
  • 3
  • 52
  • 85
2

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.

  • you are right on the point "But I agree with Frank" but I don't have access for production environment but again I will insist my higher officials regarding this thanks. – gvgvgvijayan May 14 '15 at 11:22
1

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.

Wiktor Stribiżew
  • 607,720
  • 39
  • 448
  • 563
  • @stibizhev all regex pattern working properly as i rexpected I checked it by separate the regex as follows `^13$`, `(\|13\|)?`, `(^13\||\|13$)` and checked but the actual problem is regex if `(?(1)|(^13\||\|13$))` in that the `?` is not working as I expected. – gvgvgvijayan May 14 '15 at 11:07
  • 1
    Did you select PCRE/PHP option on that site? If yes, `(?(1)` is a conditional expression. [See the explanation on that site](https://regex101.com/r/jM3kU4/1): `IF Clause (?(1)|(^13\||\|13$))`. You *cannot* paste regexps from regex101 directly into PostGre queries, you need to make sure they are compatible with this particular regex flavor. What you need is a word boundary check. In most regex engines, it is `\b`. But not in PostgreSQL. – Wiktor Stribiżew May 14 '15 at 11:13
  • The regex solution will work even in case you have stray spaces in your strings (e.g. `1 | 13 | 2`) – Wiktor Stribiżew May 14 '15 at 11:54
  • yes but actually that string is prepared using php implode and for that passing primary key fetched using yii findall AR method so in our project this issue won't occur but in general sure it will occur. And I used a_horse_with_no_name's answer also I upvoted your answer for pin pointing the space may break the regex. I'm new to postgres mostly I'm using mysql. Both follow sql standard only but can't able to achieve my expected solution is full and full lack of my knowledge over postgres function. – gvgvgvijayan May 14 '15 at 12:01