Pattern Matching with LIKE

Filtering rows in SQL often involves exact matches (=), comparisons (>, <), ranges (BETWEEN), or membership in a list (IN). However, situations arise where you need to find data based on a pattern rather than a precise value. This is common when working with text data. For instance, you might need to find all email addresses from a specific domain, all product codes starting with a certain prefix, or all names containing a particular sequence of letters.

SQL provides the LIKE operator for this exact purpose. It's used within the WHERE clause to search for specified patterns in string columns (like VARCHAR, TEXT, etc.).

Using the LIKE Operator

The basic syntax for using LIKE is straightforward:

SELECT column1, column_name
FROM table_name
WHERE column_name LIKE 'pattern';

The 'pattern' isn't just a literal string; it includes special characters called wildcards that allow for flexible matching.

Wildcards for Pattern Matching

There are two primary wildcards used with the LIKE operator:

1. The Percent Sign (%): Represents zero, one, or multiple characters. It's the most flexible wildcard.
2. The Underscore (_): Represents exactly one single character.

Let's see how these work with some examples. Imagine we have a products table with a product_name column.

• Finding names starting with 'C': To find all products whose names begin with the letter 'C', you append % after 'C'. The % matches any sequence of characters (or no characters) that follows 'C'.

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

  This would match 'Chair', 'Computer', 'Cable', 'Cap', etc.

• Finding names ending with 't': To find names ending in 't', place the % before the 't'.

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

  This could match 'Shirt', 'Hat', 'Carpet', 'Widget'.

• Finding names containing 'Desk': To find names that have 'Desk' anywhere within them, place % both before and after 'Desk'.

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

  This would match 'Standing Desk', 'Desk Lamp', 'Office Desk Set'.

• Finding names starting with 'L' and ending with 'p': You can combine literal characters and wildcards.

  SELECT product_name
  FROM products
  WHERE product_name LIKE 'L%p';
  

  This might match 'Laptop', 'Lamp'.

Now let's look at the underscore (_).

• Finding names where the second letter is 'a': The underscore _ acts as a placeholder for exactly one character.

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

  This would match 'Table', 'Lamp', 'Keyboard' (since 'a' is the second letter, and % matches the rest). It would not match 'Apple' (where 'a' is the first letter).

• Finding four-letter names starting with 'P': You can use multiple underscores.

  SELECT product_name
  FROM products
  WHERE product_name LIKE 'P___';
  

  This would match 'Pens', 'Pins', assuming those are the only four-letter product names starting with 'P'. It would not match 'Paper' (5 letters) or 'Pen' (3 letters).

Combining Wildcards

You can mix and match % and _ within the same pattern for more specific searches.

• Finding names where the second letter is 'o' and the name contains 'er' later on:

  SELECT product_name
  FROM products
  WHERE product_name LIKE '_o%er%';
  

  This could match 'Monitor', 'Power Cord', 'Folder'.

Case Sensitivity

An important detail is that the behavior of LIKE regarding case sensitivity (whether 'a' matches 'A') depends on the specific database system you are using and sometimes its configuration.

• PostgreSQL: LIKE is case-sensitive by default. It offers ILIKE for case-insensitive matching.
• MySQL: Case sensitivity depends on the table's collation settings, but often defaults to case-insensitive.
• SQL Server: Sensitivity depends on the database or column collation.

Always check the documentation for your specific database system or test it if you are unsure about case sensitivity. If you need a specific behavior (sensitive or insensitive), look for the appropriate operator or function in your database system.

Finding What Doesn't Match: NOT LIKE

Just like other comparison operators, you can negate LIKE using NOT. To find all products whose names do not start with 'S', you would use:

SELECT product_name
FROM products
WHERE product_name NOT LIKE 'S%';

Putting It Together: Example Scenario

Let's use a customers table with columns first_name, last_name, and email.

• Find customers whose first name starts with 'J':

  SELECT first_name, last_name
  FROM customers
  WHERE first_name LIKE 'J%';
  

• Find customers whose email address is from 'datastyle.com':

  SELECT first_name, email
  FROM customers
  WHERE email LIKE '%@datastyle.com';
  

• Find customers whose last name is exactly 5 letters long and ends in 's':

  SELECT last_name
  FROM customers
  WHERE last_name LIKE '____s';
  

• Find customers whose first name does not contain the letter 'a' (case-insensitive, assuming ILIKE is available or LIKE is case-insensitive):

  -- Using ILIKE (PostgreSQL example)
  SELECT first_name
  FROM customers
  WHERE first_name NOT ILIKE '%a%';
  
  -- Or if LIKE is case-insensitive (MySQL default example)
  SELECT first_name
  FROM customers
  WHERE first_name NOT LIKE '%a%';
  

The LIKE operator, combined with its wildcards % and _, provides a way to filter text data based on patterns, significantly extending the capabilities of the WHERE clause. Remember to consider case sensitivity based on your database system.