Trouble understanding a LIKE operator syntax

I got pasted a source code to extract a particular dataset and I am having a problem understanding one of the bits. Here’s the part of a query:

WHERE r LIKE 
((COALESCE(ur.title, '') || ' ' || COALESCE(urd.reviewtext, '')) ,'.* coronavirus .*|.* SARS .*', 'si')

Could you help me translate what does the inside bracket of this LIKE operator mean? Many thanks in advance.

Answer

COALESCE takes second argument if first one is NULL.

So let me got through couple of possibilities:

• ur.title is NULL, urd.reviewtext is 'review, then (COALESCE(ur.title, '') || ' ' || COALESCE(urd.reviewtext, '')) evaluates to: review ,

• if both are NULL then ((COALESCE(ur.title, '') || ' ' || COALESCE(urd.reviewtext, '')) evaluates to ‘ ,’

• ur.title is title, urd.reviewtext is 'review, then (COALESCE(ur.title, '') || ' ' || COALESCE(urd.reviewtext, '')) evaluates to: title review ,

Now, after (COALESCE(ur.title, '') || ' ' || COALESCE(urd.reviewtext, ''))(COALESCE(ur.title, '') || ' ' || COALESCE(urd.reviewtext, '')) there’s no concatenation operator, so it’s hard to guess what’s going on here, it’s syntax error.

It looks like you want to compare against multiple values, but it doesn’t work like that. You should write something like:

LIKE (COALESCE(ur.title, '') || ' ' || COALESCE(urd.reviewtext, '')) OR
LIKE '.* coronavirus .*|.* SARS .*' OR
LIKE 'si'

In first, as I broke down, doesn’t have any wildcards or any regex specific parts, so it compares literally. Same holds for last clause si, so it will match only si literally.

In second clause '.* coronavirus .*|.* SARS .*', there are regular expression specific parts as .*, but it isn’t suppoerted in SQL regex. It means generally match zero or more of any characters. You got also | there which is alterantion. But, again, it won’t work as SQL doesn’t suppoert full regex.

EDIT: it looks like Postgre operator SIMILAIR TO supports above regex.