Regular expression blacklist

Can someone explain to me the meaning of the following query:

SELECT message.message
FROM message CROSS JOIN
     Blacklist
 WHERE(message.message LIKE {fn CONCAT({fn CONCAT('%', Blacklist.Words)}, '%')});

I know what cross join means, but I want to know what exactly “{fn CONCAT({fn CONCAT(‘%’, Blacklist.Words)}, ‘%’)})” function does in this query.

Answer

From Transact SQL documentation:

CONCAT functions returns a string resulting from the concatenation, or joining, of two or more string values.

Your code uses the ODBC compatible CONCAT function that gets only 2 parameters. That’s the reason for fn prefix.

So this line:

{fn CONCAT({fn CONCAT('%', Blacklist.Words)}, '%')})

is equivalent to:

CONCAT('%', Blacklist.Words, '%')

Sample output:

If your Words field is apple, the output for LIKE statement would be:

SELECT message.message
FROM message CROSS JOIN
     Blacklist
 WHERE(message.message LIKE '%apple%');