How to use user variables in MySQL LIKE clause?

Variable initialization: Getting it Right

Correctly setting the @variable in the preamble of your query is paramount:

SET @variable := 'knock_knock'; -- Who's there? Your search string.
SELECT ... LIKE CONCAT('%', @variable, '%');


Tuning in Collation: Setting the Correct Channel

When setting up your variable, be sure it's tuned for the correct program, in other words, match the table column collation:

SELECT ... LIKE CONCAT('%', @variable COLLATE utf8_general_ci, '%');


Prepared statements: Your Listener's Guide

Boost security like it's armed with a taser by keeping prepared statements in your radio toolkit:

PREPARE stmt FROM 'SELECT ... WHERE column LIKE ?';
SET @variable := '%knock_knock%'; -- Still waiting for the punchline
EXECUTE stmt USING @variable;
DEALLOCATE PREPARE stmt;


Adding some Jazz to dynamic search patterns

Sometimes, you might need to fiddle with the search term depending on the scenario:

SET @baseSearch = 'ACDC';
SET @prefix = '%ForThose';
SET @suffix = 'RockNRoll%';
SET @searchVar := CONCAT(@prefix, @baseSearch, @suffix);


Debugging: Radio Diagnostics

For those times when your radio's a bit fuzzy and you need to troubleshoot queries, echo the variables and SQL:

SELECT CONCAT('SELECT * FROM table WHERE column LIKE ''', CONCAT('%', @variable, '%'), '''');


Security: Keeping Your Frequency Clear

Prevent SQL injection by treating user inputs as a potential trojan horse. Ensure you sweep for bugs or use parameterized queries:

-- Escaping user inputs, the shampoo of SQL input sanitation
SET @userInput := 'ThisIsDefinitelyNotASQLInjection';
SET @safeInput := escape_function(@userInput);
SELECT ... WHERE column LIKE CONCAT('%', @safeInput, '%');


Enhancing your SQL Tuning Skills

Sharpen your SQL skills by considering these additional techniques and precautions when incorporating user variables with LIKE.

How to Escape Your Characters

Escape literal % or _ in user variables for them to stop playing hide-and-seek in searches:

SET @userInput := '20% discount'; -- Now discounted by constraint '%'
SELECT ... WHERE column LIKE CONCAT('%', REPLACE(@userInput, '%', '\\%'), '%');


The Ghost in Your Datasets: NULL values

NULL values lurk in variables and columns like extra terrestrial signals. Use COALESCE or IFNULL to manage them:

SELECT ... WHERE column LIKE CONCAT('%', COALESCE(@variable, 'SETI_not_found'), '%');


The Art of dynamic variable assignment

Assign patterns directly to the variable in a SELECT INTO statement for elaborate concatenations:

SELECT CONCAT('%', @userInput, '%') INTO @likePattern;
SELECT ... WHERE column LIKE @likePattern;


The Case of Sensitivity

Your search's case sensitivity could be pivotal:

SELECT ... WHERE column LIKE CONCAT('%', @variable COLLATE utf8_bin, '%');


To bypass the case concern, adhere to a case-insensitive collation.

Advanced Variable Jockeying

Churn out advanced pattern matches by fusing multiple variables:

SET @start := '_start' COLLATE utf8_general_ci;
SET @end := '_end' COLLATE utf8_general_ci;
SELECT ... WHERE column LIKE CONCAT(@start, '%', @end);
