🧩 SQL LIKE – Pattern Matching with Wildcards in SQL

🧲 Introduction – Why Use SQL LIKE?

The LIKE operator in SQL is used to search for specific patterns in string values. It’s a powerful tool for performing partial matches, flexible filtering, and simple text analysis.

🎯 In this guide, you’ll learn:

• How to use LIKE with % and _ wildcards
• Combine LIKE with WHERE and logical conditions
• Handle case-sensitivity across different databases
• Real-world examples of pattern matching

---

✅ 1. Basic LIKE Syntax

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

✅ Returns names starting with “A”.

---

🔧 2. Pattern Matching Wildcards

Symbol	Meaning	Example	Matches
%	Zero or more characters	'J%'	John, Jake, Jasmine
_	Exactly one character	'J_n'	Jon, Jan, Jim

---

🧪 3. Examples of SQL LIKE Patterns

-- Names that start with A
SELECT * FROM employees WHERE name LIKE 'A%';

-- Emails ending with '.com'
SELECT * FROM contacts WHERE email LIKE '%.com';

-- Cities with 'new' in the middle
SELECT * FROM locations WHERE city LIKE '%new%';

-- Postal codes that are exactly 5 digits
SELECT * FROM addresses WHERE postal_code LIKE '_____'  -- five underscores

---

🔁 4. Using NOT LIKE

SELECT * FROM products WHERE name NOT LIKE '%apple%';

✅ Returns all products that do not contain “apple”.

---

⚠️ 5. Case Sensitivity in LIKE

• MySQL: Case-insensitive by default (unless BINARY keyword used)
• PostgreSQL: Case-sensitive by default
• SQL Server: Depends on the collation

💡 Use ILIKE in PostgreSQL for case-insensitive matching.

---

🧠 6. LIKE with Logical Operators

SELECT * FROM users
WHERE name LIKE 'A%' OR name LIKE 'B%';

✅ Combines multiple LIKE patterns using OR.

---

📘 Best Practices

✅ Do This	❌ Avoid This
Use % and _ precisely	Overusing % at both ends for large tables
Use ILIKE in PostgreSQL if needed	Assuming all LIKE queries are case-insensitive
Optimize indexed columns	Ignoring performance impact of wildcards

---

📌 Summary – Recap & Next Steps

SQL LIKE is your go-to tool for performing text pattern matching. With % and _, you can construct expressive search filters across a wide variety of string data.

🔍 Key Takeaways:

• % matches any number of characters; _ matches exactly one
• Combine LIKE with WHERE, NOT, and OR for flexible searches
• Behavior varies based on RDBMS case sensitivity settings

⚙️ Real-World Relevance:
Used in search bars, product filters, customer lookup, and data cleansing operations.

---

❓ FAQ – SQL LIKE

❓ What is the difference between LIKE and =?

✅ LIKE supports pattern matching; = checks for an exact match.

❓ Does LIKE support case-insensitive search?

✅ Depends on your database engine. Use ILIKE for PostgreSQL.

❓ Can I use LIKE with numbers?

✅ Yes, but numbers are implicitly cast as strings.

❓ Can I match only one character?

✅ Use _ (underscore) to match exactly one character.

❓ What is the performance impact of LIKE?

✅ It can be slow with leading % wildcards unless indexed or optimized.

---

Share Now :