SQL LIKE Operator

Welcome to The Coding College, your go-to platform for mastering coding and programming concepts! In this tutorial, we’ll explore the SQL LIKE operator, which allows you to search for patterns in your data.

What Is the SQL LIKE Operator?

The SQL LIKE operator is used in a WHERE clause to search for a specified pattern in a column. It’s especially useful for partial matches, such as finding all names starting with a particular letter or containing a specific substring.

Syntax

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

Wildcards Used in LIKE

  1. % (Percentage Sign):
    • Represents zero, one, or multiple characters.
    • Example: 'A%' matches any value starting with “A”.
  2. _ (Underscore):
    • Represents a single character.
    • Example: 'A_' matches “AB”, “AC”, etc., but not “ABC”.

Example Table: customers

customer_idnameemailcity
1Alice Brown[email protected]New York
2Bob White[email protected]Los Angeles
3Charlie Day[email protected]Chicago
4Diana Prince[email protected]Houston
5Evan Stone[email protected]Phoenix

Examples of LIKE Usage

1. Finding Names Starting with “A”

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

Result:

customer_idnameemailcity
1Alice Brown[email protected]New York

2. Finding Emails Containing “stone”

SELECT *  
FROM customers  
WHERE email LIKE '%stone%';

Result:

customer_idnameemailcity
5Evan Stone[email protected]Phoenix

3. Finding Cities Ending with “o”

SELECT *  
FROM customers  
WHERE city LIKE '%o';

Result:

customer_idnameemailcity
3Charlie Day[email protected]Chicago

4. Finding Names with Exactly 5 Characters

SELECT *  
FROM customers  
WHERE name LIKE '_____';

Result:

customer_idnameemailcity
2Bob[email protected]Los Angeles

Combining LIKE with AND/OR

Example: Finding Names Starting with “A” or Emails Containing “stone”

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

Result:

customer_idnameemailcity
1Alice Brown[email protected]New York
5Evan Stone[email protected]Phoenix

Using NOT LIKE

To exclude patterns, use NOT LIKE.

Example: Exclude Emails Ending with “.com”

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

Real-World Applications

  • Search Filters:
    • Allow users to search by partial matches (e.g., product names, cities).
SELECT * FROM products WHERE product_name LIKE '%phone%';
  • Data Validation:
    • Identify rows with unusual patterns (e.g., incorrect email formats).
SELECT * FROM users WHERE email NOT LIKE '%@%.%';
  • Marketing Insights:
    • Find customers in cities matching specific patterns.
SELECT * FROM customers WHERE city LIKE 'New%';
  • Content Filtering:
    • Search blog posts, descriptions, or comments for specific keywords.
SELECT * FROM articles WHERE content LIKE '%SQL%';

Best Practices

  1. Avoid Overuse of Wildcards:
    • Use % only where necessary to improve query performance.
  2. Combine with Indexes:
    • For large datasets, indexing columns used in LIKE queries enhances performance.
  3. Case Sensitivity:
    • In most databases, LIKE is case-insensitive (e.g., MySQL). Use ILIKE in case-sensitive databases like PostgreSQL.
  4. Test with Small Data:
    • Validate queries on a small dataset before running on production.

Conclusion

The SQL LIKE operator is a versatile tool for pattern matching in databases. Whether you’re searching for specific text, filtering by patterns, or excluding unwanted data, LIKE helps make your queries precise and effective.

For more SQL tutorials and coding guides, visit The Coding College and boost your programming skills!

Leave a Comment