🧠 SQL LIKE and Wildcards: Flexible Pattern Matching Made Easy

When working with databases, there are times you want to search for a partial match instead of an exact value. That’s where SQL’s LIKE operator and wildcard characters come in handy.

This article will walk you through how LIKE works in SQL, how to use the wildcard characters % and _, and provide real-world examples for better understanding. Whether you’re querying names, emails, or anything else, these tools are essential for flexible searching.

📘 What is the LIKE Operator in SQL?

The LIKE operator in SQL is used in the WHERE clause to search for a specific pattern in a column. It is not case-sensitive in most databases (like MySQL and PostgreSQL by default), but in some systems like Oracle, case-sensitivity matters unless configured otherwise.

SELECT column_name
FROM table_name
WHERE column_name LIKE 'pattern';

Instead of matching the whole string exactly, LIKE allows you to use wildcard characters to define patterns.

🔣 Wildcards in SQL

1. % (Percent Sign)

• Represents zero or more characters.
• Can be used anywhere in the pattern.

Example:
LIKE 'A%' finds any value that starts with “A”.

2. _ (Underscore)

• Represents a single character.
• Used when you want to match a character at a specific position.

Example:
LIKE '_at' matches “Cat”, “Hat”, “Bat”, but not “Flat”.

🎓 Example Table: employees

🔍 Examples of Using LIKE

✅ 1. Names Starting with “A”

SELECT name
FROM employees
WHERE name LIKE 'A%';

Result:

✅ 2. Emails Ending with “gmail.com”

SELECT email
FROM employees
WHERE email LIKE '%gmail.com';

Result:

✅ 3. Names Containing “ar”

SELECT name
FROM employees
WHERE name LIKE '%ar%';

Result:

✅ 4. Emails with Single Character Before ”@“

SELECT email
FROM employees
WHERE email LIKE '_%@%';

This checks for emails that start with exactly one character before the @.

📐 Pattern Matching Examples

🧯 Common Mistakes to Avoid

❌ 1. Forgetting Wildcards

-- Wrong: matches only exact string "gmail.com"
WHERE email LIKE 'gmail.com';

Fix:

WHERE email LIKE '%gmail.com';

❌ 2. Using = Instead of LIKE

If you’re searching for a pattern or partial match, LIKE is required.

-- Wrong:
WHERE name = '%ar%';
-- Right:
WHERE name LIKE '%ar%';

❌ 3. Expecting Case Sensitivity (varies by DBMS)

• MySQL: LIKE is case-insensitive by default.
• PostgreSQL: Use ILIKE for case-insensitive search.
• SQL Server: Depends on collation settings.

🛠 Real-World Use Cases

🧾 1. Filter Emails from Specific Domain

SELECT name, email
FROM employees
WHERE email LIKE '%@gmail.com';

🔍 2. Find Customers with Short Names

SELECT name
FROM customers
WHERE name LIKE '___';  -- Exactly 3 characters

🗂 3. Search Product Names with “Pro”

SELECT product_name
FROM products
WHERE product_name LIKE '%Pro%';

💬 Combining LIKE with NOT

🛑 Exclude Emails from Gmail

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

This returns all email addresses not ending with “@gmail.com”.

🔄 Combine LIKE with AND/OR

SELECT name
FROM employees
WHERE name LIKE 'A%' OR name LIKE 'B%';

Returns names starting with A or B.

🧠 Summary Table

✅ Final Thoughts

The LIKE operator and wildcards (% and _) are essential tools for anyone working with SQL. They allow for flexible, powerful searches that go beyond simple exact matches. Whether you’re filtering names, emails, products, or any other text-based column, understanding how to use LIKE effectively will save you time and make your queries much more powerful.

Take time to experiment with different combinations and patterns in your database — the more you use them, the more natural they’ll become!