postgresql posix regexp

Question

I have the following rows of data:

login;visit_homepage;buy

register;login;buy;visit_homepage

I want to match all rows that have login event followed by buy event. There might be some other events between the login and buy events. Buy can be last event and login can be first event. I need exact match of login and buy not wildcard.

I have tried this:

SELECT * FROM events WHERE events_list ~ 'login;.*buy;?.*$';

but i think it does not cover all cases.

Thanks

I have tried this: SELECT * FROM events WHERE events_list ~ 'login;.*buy;?.*$'; — Plamen Paskov
– Plamen Paskov, Commented Feb 21, 2017 at 15:36

Niitaku · Accepted Answer · 2017-02-21 15:49:58Z

2

This regex should match the rows:

\mlogin\M.*?\mbuy\M

It searches the word login followed later by buy.

\m matches the beginning of a word
\M matches the end of a word

Usage of the boundaries prevent matching login or buy that is composing a word (ex. foologin).

More info on these POSIX escapes in PostgreSQL here.

answered Feb 21, 2017 at 15:49

Niitaku

8359 silver badges19 bronze badges

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

1 Comment

Abdul Hameed Over a year ago

better than mine to avoid foologin cases. One more good practice learnt.

Abdul Hameed · Accepted Answer · 2017-02-21 15:43:41Z

0

Try this regex to select both the rows

login;.*buy.*$ or login.*buy.*$

you can check the demo here

Or you can simplify it even more by using login.*buy

answered Feb 21, 2017 at 15:43

Abdul Hameed

99212 silver badges28 bronze badges

2 Comments

Plamen Paskov Over a year ago

it will not work correctly because .* will match anything before and after buy.

Abdul Hameed Over a year ago

I want to match all rows that have login event followed by buy event.. that is your requirement. my answer matches all rows containing event list having login followed by buy. i dont get where it is going wrong.. please provide more test cases on input output and the match you need

Collectives™ on Stack Overflow

postgresql posix regexp

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related