MySQL LIKE

MySQL LIKE Operator

The LIKE operator in MySQL is used to search for a specified pattern in a column. It is typically used with SELECT, UPDATE, or DELETE statements to filter results based on partial matches.

Syntax

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE pattern;

Pattern Matching

• %: Represents zero, one, or multiple characters.
• _: Represents a single character.

Examples

1. Basic Usage of %

Find all customers whose names start with "A":

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

• 'A%': Matches "Alice", "Andrew", "Alex", etc.

2. Match Ending Pattern

Find all customers whose names end with "n":

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

• '%n': Matches "John", "Ethan", "Megan", etc.

3. Match Middle Pattern

Find all customers whose names contain "an":

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

• '%an%': Matches "Andrew", "Megan", "Daniel", etc.

4. Using _ for Single Character

Find all customers whose names have "Jo" followed by exactly one character:

SELECT * 
FROM customers 
WHERE name LIKE 'Jo_';

• 'Jo_': Matches "Jon", "Joe", etc.

5. Case Sensitivity

The LIKE operator is case-insensitive by default in MySQL. For case-sensitive pattern matching, use the BINARY keyword.

Case-Insensitive:

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

Case-Sensitive:

SELECT * 
FROM customers 
WHERE name LIKE BINARY 'a%';

6. Escaping Special Characters

To match % or _ literally, use the ESCAPE keyword.

Example: Find names containing "50%":

SELECT * 
FROM products 
WHERE name LIKE '50\%%' ESCAPE '\';

7. Combining LIKE with Other Conditions

Find customers whose names start with "A" and live in "New York":

SELECT * 
FROM customers 
WHERE name LIKE 'A%' AND city = 'New York';

Practical Examples

1. Search for Email Domains

Find all users with Gmail accounts:

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

2. Search for Phone Numbers with Specific Pattern

Find phone numbers starting with "123":

SELECT * 
FROM contacts 
WHERE phone LIKE '123%';

Performance Considerations

• Indexes: Queries using LIKE with a leading wildcard ('%pattern') cannot use indexes efficiently, potentially slowing down searches.
• Full-Text Search: For more complex pattern matching, consider using MySQL's full-text search instead of LIKE.

Let me know if you need more examples or specific use cases!