Previous
Written by: rJTutorial Team | Last Updated:

SQL LIKE

The LIKE operator in SQL is a fundamental tool used for pattern matching within a WHERE clause. While the standard equals operator (=) searches for an exact match, LIKE allows you to find data that matches a partial string or a specific format. It is most commonly used with wildcards to filter text data based on flexible criteria.

Developer Tip: Think of the LIKE operator as a simplified version of Regular Expressions. It’s perfect for search bars, filtering lists, or finding records where you only remember a piece of the information.

 

Basic Syntax of LIKE Operator

SELECT column1, column2, ...
FROM table_name
WHERE column_name LIKE 'pattern';
  • column_name: The specific column you want to search through.
  • pattern: The string literal or character pattern you are looking for, which usually includes wildcard characters.
Best Practice: Use LIKE only when you need partial matches. If you know the exact value, use the = operator instead, as it is significantly faster for the database to process.

 

Wildcards in LIKE

To use LIKE effectively, you must understand the two primary wildcards:

  1. % (Percent Sign): Represents zero, one, or multiple characters. For example, 'a%' matches "apple," "arc," or just "a."
  2. _ (Underscore): Represents exactly one single character. For example, 'h_t' matches "hat" or "hot," but not "heat."
Watch Out: Case sensitivity for the LIKE operator depends on your database engine. SQL Server and MySQL are often case-insensitive by default, while PostgreSQL is case-sensitive (requiring ILIKE for case-insensitive searches).

 

Key Features of LIKE

1. Match Values Starting with Specific Characters

This is useful for searching for surnames in a directory or categories in a catalog.

SELECT name  
FROM employees  
WHERE name LIKE 'A%';
  • Retrieves all employee names starting with A, such as "Alice," "Aaron," or "Al."
Common Mistake: Forgetting that 'A%' will also match the single character "A" if it exists in the column.

2. Match Values Ending with Specific Characters

This pattern is frequently used to find specific file types or domain extensions.

SELECT product_name  
FROM products  
WHERE product_name LIKE '%X';
  • Retrieves product names ending with X, such as "Model X" or "Linux."

3. Match Values Containing Specific Characters

This is the most common pattern for search bars, where you want to find a keyword regardless of where it appears in the string.

SELECT customer_name  
FROM customers  
WHERE customer_name LIKE '%John%';
  • Retrieves customer names containing John anywhere, including "John Smith," "Elton John," or "Little Johnny."

4. Match Values with a Single Character

Use _ when you know the length of the string but need to allow for variations in a specific position.

SELECT name  
FROM employees  
WHERE name LIKE 'J_n';
  • Retrieves names like Jon, Jan, or Jen. This is helpful for finding entries with common typos or slight spelling variations.

5. Combine Wildcards

You can combine % and _ to create highly specific filters.

SELECT product_name  
FROM products  
WHERE product_name LIKE 'A__%';
  • Retrieves product names starting with A that have at least 3 characters in total. The two underscores ensure there are at least two characters following the 'A'.

6. NOT LIKE

The NOT LIKE operator allows you to filter out records that match a certain pattern, which is great for cleaning up report data.

SELECT name  
FROM employees  
WHERE name NOT LIKE 'A%';
  • Retrieves all employee names that do not start with A.
Watch Out: Using a wildcard at the beginning of a pattern (e.g., LIKE '%something') prevents the database from using indexes efficiently. This can lead to slow queries on very large tables.

 

Example Queries

1. Match Names Starting with 'S'

SELECT name  
FROM employees  
WHERE name LIKE 'S%';
  • Retrieves names starting with S, like "Steven" or "Sarah."

2. Match Cities Ending with 'o'

SELECT city  
FROM customers  
WHERE city LIKE '%o';
  • Retrieves cities like "Tokyo," "Orlando," or "Oslo."

3. Match Emails Containing 'gmail'

SELECT email  
FROM users  
WHERE email LIKE '%gmail%';
  • Useful for identifying users who signed up with a specific email provider.

4. Match Product Codes with Single Character Variations

SELECT product_code  
FROM products  
WHERE product_code LIKE 'P_1';
  • Retrieves product codes like P01, P11, or P21, where the middle character changes.

5. Exclude Names Containing 'Test'

SELECT name  
FROM users  
WHERE name NOT LIKE '%Test%';
  • A practical example for filtering out "Test User" or "Draft Test" accounts from production results.

 

Summary

The LIKE operator is a powerful tool for pattern matching in SQL. By mastering the % and _ wildcards, you can perform flexible searches that go far beyond simple equality. Just remember to use it wisely while LIKE is incredibly useful, using leading wildcards on massive datasets can impact performance, so always consider your indexing strategy.

Previous