Concatening LIKE with Stored Procedure's parameter using MySQL

Question

The goal

Concatenate a parameter with a % operator of LIKE using MySQL.

The problem

There's the following query on my database and it returns me the results very well:

SELECT people.LOGIN, people.EMAIL FROM people
WHERE people.LOGIN LIKE 'o%'
ORDER BY people.LOGIN ASC
LIMIT 10;

But I want to call this function using a Stored Procedure, and instead of use 'o%', I want to use '<myvariable>%' and I'm not managing to do this.

The scenario

I've tried the following on my procedure:

BEGIN
    SELECT people.EMAIL FROM people
    WHERE people.LOGIN LIKE CONCAT(expression, '%')
    ORDER BY people.LOGIN ASC
    LIMIT 10;
END

The result? Empty. This query returns me nothing.

Someone has any idea to help me?

Answer 1

Often what actually gets passed in is not what we think.

Check that the parameter:

• is not null
• does not have leading or trailing whitespace

Try this to guard against the above:

WHERE people.LOGIN LIKE CONCAT(TRIM(IFNULL(expression, '')), '%')

Answer 2

You need to use prepared statements.

Create a string with a query, and execute it using prepared statements. Something like this -

SET @query = CONCAT('SELECT * FROM table1 WHERE column1 LIKE ''', expression, '%''');
PREPARE stmt FROM @query;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;