LIKE in MySQL

The LIKE operator in MySQL is used to search for a specified pattern in a column using wildcards (% and _).

1. Syntax

SELECT column1, column2, ...FROM table_nameWHERE column_name LIKE pattern;

✅ Finds rows where column_name matches the pattern.
✅ Case-insensitive for most MySQL storage engines (like InnoDB).

2. Wildcards in LIKE

3. Example Table: customers

4. Using % (Multiple Characters)

Find names that start with "A"

SELECT * FROM customers WHERE name LIKE 'A%';

✅ Matches: Alice

Find emails that end with "@gmail.com"

SELECT * FROM customers WHERE email LIKE '%@gmail.com';

✅ Matches: alice@gmail.com, dave123@gmail.com

5. Using _ (Single Character)

Find names with "a" as the second letter

SELECT * FROM customers WHERE name LIKE '_a%';

✅ Matches: Charlie, David

Find 3-letter names

SELECT * FROM customers WHERE name LIKE '___';

✅ Matches: Bob

6. Case Sensitivity

• By default, LIKE is case-insensitive (for VARCHAR, TEXT in utf8_general_ci collation).
• For case-sensitive search, use BINARY:

SELECT * FROM customers WHERE name LIKE BINARY 'alice';

✅ Matches only lowercase "alice", not "Alice".

7. Using NOT LIKE

Find customers whose email is NOT Gmail

SELECT * FROM customers WHERE email NOT LIKE '%@gmail.com';

✅ Excludes Gmail users.

8. Combining LIKE with OR

Find names that start with 'A' or end with 'd'

SELECT * FROM customers WHERE name LIKE 'A%' OR name LIKE '%d';

9. Performance Tips

⚡ Avoid leading % ('%search%' is slow) – Use indexed columns whenever possible.
⚡ Use FULLTEXT index for large text searches instead of LIKE for better performance.

10. Key Takeaways

✅ LIKE is used for pattern matching with % and _.
✅ % matches any number of characters, _ matches a single character.
✅ Case-insensitive by default. Use BINARY for case-sensitive searches.