Will pass variables in SELECT statement in MySQL 8

Question

I'm trying a simple select statement with variables. If I change the statement like concat_ws('%', @S, '%'); it works fine. to a string. It seems that the select statement does not select the SET variable. Thank you for your help. I am using Mysql80 workbench.

SET @S = "product";
SELECT distinct idproducts FROM mgjtest.vorutaflamedsamheit
WHERE productname like concat_ws('%', @S, '%');
````````````````````````````````````````````````````````````````

Answer 1

Just use CONCAT to ensure wildcard characters on both sides of the variable value. Otherwise, first using CONCAT_WS as the delimiter argument returns a double wildcard at the end of the string, is equivalent to the single wildcard and produces undesired results.

LIKE 'product%%'

LIKE 'product%'

However, CONCAT will return wildcards as you would expect:

LIKE '%product%'