MySQL WHERE Clause: Using the LIKE Clause

Understanding the LIKE Clause

The LIKE clause is used in the WHERE statement to filter rows where a column's value matches a specified pattern.

Patterns can include wildcards that represent one or more characters.

• Syntax: column_name LIKE 'pattern'
• Patterns are enclosed in single quotes.
• Common wildcards: % and _

Using LIKE with Examples

Let's look at practical examples to understand how LIKE works in queries.

Case Sensitivity and Collation

By default, LIKE is case-insensitive in MySQL if the column uses a case-insensitive collation.

To perform case-sensitive searches, you can use the BINARY keyword or change the collation.

• Case-insensitive: WHERE name LIKE 'a%' matches 'Apple' and 'apple'.
• Case-sensitive: WHERE BINARY name LIKE 'a%' matches only lowercase 'a'.

Combining LIKE with Other Conditions

You can combine LIKE with AND, OR, and NOT operators to create complex filters.

• Example: WHERE name LIKE 'a%' AND status = 'active'
• Example: WHERE name NOT LIKE '%test%'