sqlregexpostgresqlposixposix-ere

How to use posix regexp operator for substring match


I'm trying to move from ILIKE to Postgres ~* operator. Tables have bpchar columns (cannot changed). Tried in Postgres 12

create temp table test ( tasudok char(25), nimetus char (100) ) on commit drop ;
insert into test values ('123','Teie Arve 123 tasumine');
select nimetus ~* '*. '||tasudok||' *.' from test;

but got error

ERROR: invalid regular expression: quantifier operand invalid

How to use substring match with ~* operator?


Solution

  • Your *. was probably supposed to be .* and it's reversed for some reason. The leading * is expecting to be preceded by something, but it's right at the start of the pattern, hence the error. Flip those and it'll work just fine:
    demo at db-fiddle

    select nimetus ~* ('.* '||tasudok||' .*') from test;
    

    9.7.3.1. Regular Expression Details has a note warning against just that:

    A quantifier cannot immediately follow another quantifier, e.g., ** is invalid. A quantifier cannot begin an expression or subexpression or follow ^ or |.

    You also probably want to parenthesize the concatenated strings, otherwise they get evaluated left-to-right. As a result, only the first one's matched, and the resulting boolean gets cast to text, then concatenated to tasudok, then to ' *.':

    nimetus tasudok operation result
    'Teie Arve 123 tasumine' '123' nimetus ~* '.* '||tasudok||' .*' true123 .*
    'Teie Arve 123 tasumine' '123' (nimetus ~* '.* ')||tasudok||' .*' true123 .*
    'Teie Arve 123 tasumine' '123' nimetus ~* ('.* '||tasudok||' .*') true
    'Teie Arve 456 tasumine' '123' nimetus ~* '.* '||tasudok||' .*' true123 .*
    'Teie Arve 456 tasumine' '123' nimetus ~* ('.* '||tasudok||' .*') false