Search code examples
regexpostgresqlpsql

postgres match with regex

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';
Solution

  • 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.