MySQL LIKE Operator
MySQL LIKE Operator
The LIKE operator in MySQL is used in a WHERE clause to search for a specified pattern in a column. It is commonly used for text/filter-based searches.
✅ Syntax
🔍 LIKE Uses Wildcards
| Wildcard | Meaning |
|---|---|
% |
Represents zero or more characters |
_ |
Represents exactly one character |
🧠 Example Table: employees
| id | name | department |
|---|---|---|
| 1 | John Smith | HR |
| 2 | Emma Stone | IT |
| 3 | Raj Kumar | Finance |
| 4 | Samuel Lee | IT |
| 5 | Johnny Ray | HR |
✔ Examples Using LIKE
1. Search for Names Starting with a Letter
✔ Returns: John Smith, Johnny Ray
2. Search for Names Ending with a Letter
✔ Matches any name ending with n
3. Search for Names Containing a Pattern Anywhere
✔ Matches: Emma Stone, Samuel Lee (because “am” appears in the name)
4. Using _ Wildcard (Single Character Match)
✔ Finds names where the second letter is ‘a’, such as:
-
Raj Kumar
-
Samuel Lee
5. Searching for Specific Length Patterns
✔ Matches names with at least 5 characters (_ repeated)
6. Search With NOT LIKE
✔ Returns all employees whose names do NOT start with J.
🎯 LIKE with Case Sensitivity
-
In most MySQL configurations, LIKE is case-insensitive for text columns (e.g., VARCHAR).
-
But in case-sensitive collations, use:
📌 Summary
| Pattern Example | Meaning |
|---|---|
'A%' |
Begins with A |
'%A' |
Ends with A |
'%A%' |
Contains A |
'A__%' |
Starts with A and has at least 3 characters |
'_%A' |
Second-to-last character is A |
