CODEWITHSUNDEEP
regexpostgresqlposix
Plamen Paskov
Plamen Paskov

Reputation: 144

postgresql posix regexp

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

Upvotes: 0

Views: 131

Answers (2)

Niitaku
Niitaku

Reputation: 835

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.

Upvotes: 2

Abdul Hameed
Abdul Hameed

Reputation: 992

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

Upvotes: 0

Related Questions