PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – 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?

Tags:

2

Answers


  1. 0

    Simply:

    SELECT nimetus ~* tasudok FROM test;

    You do not need to pad the pattern at all like you would have to with ILIKE. Equivalent 1:

    SELECT nimetus ILIKE '%' || tasudok || '%' FROM test;

    1 Except for special characters in the "pattern" column tasudok that would have different meaning. See:

    Operator precedence

    Notably, ILIKE and LIKE rank one step below default operators in operator precedence, so no parentheses are required for my expression. Strictly speaking, LIKE is not an operator, but an SQL construct, that is implemented with the Postgres operator ~~ internally. (~~* for ILIKE, !~~ for NOT LIKE, !~~* for NOT ILIKE)

    Also notably, those internal operators have default operator precedence (as do the regex operators ~, ~*, !~, and !~*). So parentheses are required around the concatenated string for those.

    It’s just that you do not need to concatenate anything to begin with for your example. Would be expensive noise.

    Login or Signup to reply.
  2. 0

    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
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search