? MySQL LIKE

? Quick Overview

The LIKE operator matches text using simple patterns. Use % for any sequence of characters and _ for a single character. It's handy for partial string matches in WHERE clauses.

? Key Concepts

LIKE: pattern matching operator used in WHERE.
%: matches zero or more characters.
_: matches exactly one character.
Case Sensitivity: MySQL LIKE is usually case-insensitive depending on collation; use BINARY to force case-sensitivity.

? Syntax / Theory

? View Code Example

-- Syntax: select columns where a column matches a pattern
SELECT column1, column2
FROM table_name
WHERE column_name LIKE pattern;

? Code Examples

? View Code Example

-- Find students whose names start with 'J'
SELECT * FROM students
WHERE name LIKE 'J%';

? View Code Example

-- Find students whose names end with 'son'
SELECT * FROM students
WHERE name LIKE '%son';

? View Code Example

-- Find students whose names contain 'an'
SELECT * FROM students
WHERE name LIKE '%an%';

? View Code Example

-- Use underscore to match a single character (e.g., any name with 'a' as second char)
SELECT * FROM students
WHERE name LIKE '_a%';

? View Code Example

-- Force case-sensitive match using BINARY
SELECT * FROM students
WHERE BINARY name LIKE 'J%';

? Explanation / Live Output

What happens

Using LIKE 'J%' returns rows where name begins with "J". Using '%son' returns rows ending with "son". The % and _ wildcards let you control how strict or loose the match is.

Performance note

Leading wildcards (e.g., %term) prevent index usage on the column and can be slow on large tables. Consider full-text indexes or computed columns for heavy pattern-search workloads.

✅ Tips & Best Practices

Use LIKE 'abc%' when you can (no leading %) so indexes may be used.
For complex patterns, consider regular expressions (REGEXP) if your DB supports it.
Remember collation: case sensitivity depends on the column's collation. Use BINARY if needed.

? Try It Yourself / Practice Tasks

Write a query to find records where a column starts with 'a' and ends with 'z'. (Hint: combine a% and %z patterns.)
Find all rows where the third character is 'r' using _.
Experiment with BINARY to compare case-sensitive vs case-insensitive results.