postgres match with regex

Question

I have a column containing values like this

views.decorators.cache.cache_header.APISetOne.596e986edcf211a323dbac38fe3654b8.en-us.UTC
views.decorators.cache.cache_page.APISetTwo.GET.1fc365e1f67d39935b199c133fada23b.ce596bddd33104b703521c71eb2e32c6.en-us.UTC

It is something like 'view.decorators.cache.cache_{page|header}.{if header then None in this part, if not 'GET'}.{key_prefix_name}.{some_hash_that changes_everytime}.{last_hash}.en-us.UTC' #i don't care about en-us.UTC thing.

I have access to key_prefix_name and the last_hash.

I want to search across rows that contains a particular key_prefix_name and the last_hash.

I am struggling to create a regex that matches this. So far, I have come up with something like:

select col1 from my_table where col1 ~* 'views.decorators.cache.cache_page.**APISetOne**.GET.[a-z,1-0].**ce596bddd33104b703521c71eb2e32c6**.en-us.UTC';

Something like views\.decorators\.cache\.cache_(?:page\.APISetOne\.GET|header)\.([[:alnum:]]+)\.([[:alnum:]]+)\.en-us\.UTC? Or maybe views\.decorators\.cache\.cache_(?:page|header)\.([[:alnum:]]+)\.(?:GET\.)?([[:alnum:].]+)\.en-us\.UTC? What are the expected results? — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Dec 21, 2021 at 8:08
@WiktorStribiżew Thanks it worked without the "\" before each dot though, I ended up with something like 'views.decorators.cache.cache_page.THE_PREFIX.GET.([[:alnum:]]+).THE_LAST_HASH.en-us.UTC' Thanks for helping me out with that alnum part, I suck at regex — reindeer
– reindeer, Commented Dec 21, 2021 at 9:33
You need to escape the ., or it will match any char. Have a look here. — Wiktor Stribiżew
– Wiktor Stribiżew, Commented Dec 21, 2021 at 10:30

Wiktor Stribiżew · Accepted Answer · 2021-12-21 10:35:41Z

1

You can probably use

views\.decorators\.cache\.cache_(?:page|header)\.([[:alnum:]]+)\.(?:GET\.)?([[:alnum:]]+)(?:\.[[:alnum:]]+)?\.en-us\.UTC

See the regex demo. It matches

views\.decorators\.cache\.cache_ - a fixed views.decorators.cache.cache_ string
(?:page|header) - page or header
\. - a dot
([[:alnum:]]+) - Group 1: one or more alphanumerics
\. - a dot
(?:GET\.)? - an optional GET. string
([[:alnum:]]+) - Group 2: one or more alphanumerics
(?:\.[[:alnum:]]+)? - an optional occurrence of a . and then one or more alphanumeric char sequence
\.en-us\.UTC - a .en-us.UTC string.

answered Dec 21, 2021 at 10:35

Wiktor Stribiżew

631k41 gold badges502 silver badges632 bronze badges

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

1 Comment

reindeer Over a year ago

Yes, I added the backslash and it works, I am executing this from python so, I must have had some typo before I guess. And, thanks for the detailed answer, I seem to understand some now. I appreciate it, thank you!

Collectives™ on Stack Overflow

postgres match with regex

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related