Use LIKE %..% with field values in MySQL

Harnessing LIKE and CONCAT

The LIKE operator, when coupled with the CONCAT function, flexibly adapts to the dynamism of your search operations. Use this construct to embed a field's value within the LIKE pattern:

SELECT column1 FROM table1 
WHERE column1 LIKE CONCAT('%', (SELECT column2 FROM table2 WHERE condition), '%');


This code snippet demonstrates how column2's value from table2 is dynamically merged into the LIKE clause in table1. This greatly enhances your text search operations.

Enabling dynamic search conditions

In advanced scenarios, joining tables based on text search conditions can turn out to be supremely beneficial:

SELECT t1.column, t2.column FROM table1 t1
JOIN table2 t2 ON t1.column LIKE CONCAT('%', t2.column, '%');


The advantage here is that you can form relations between tables based on partial text matches. Isn't that cool?

SQL joins using partial text matches

Now, let's turn the coolness up a notch with an implicit join, paired with LIKE and CONCAT:

SELECT t1.column, t2.column 
FROM table1 t1, table2 t2 
WHERE t1.column LIKE CONCAT('%', t2.column, '%');


Swift and clean, isn't it? So, your LIKE pattern can be robust and flexible simultaneously!

Nailing the syntax: String concatenation

Remember that in SQL, string concatenation within CONCAT() is handled using commas, not plus symbols. Following the right syntax is crucial to eliminate errors in string operations.

Understanding performance impact and optimisation

Here's where SQL starts behaving like a spoilt child: leading wildcards in LIKE searches can sometimes lead to performance issues by preventing index optimization. This might slow down search operations, especially in large datasets. Take it easy, champ!

Case sensitivity: Collations

The LIKE operator's case sensitivity is determined by the collation of the database or the specific column in use. With a case-insensitive collation, LIKE '%apple%' will match 'Apple Pie', 'apple tree', and even 'PiNeApPle'!

Escaping the escape room: Special characters

In the SQL universe, % and _ have special powers. To neutralize these powers and search for these characters as literals, you need to escape them:

SELECT column FROM table WHERE column LIKE '%25\%%' ESCAPE '\';


This is your key to run a SQL search for the string "25%" within column.