LIKE and Wildcards

The LIKE operator in SQL is used for pattern matching to find matching string values. It’s often used in a WHERE clause to search for a specified pattern in a column.

Basic Syntax:

The syntax for using the LIKE operator is:

SELECT column_names
FROM table_name
WHERE column_name LIKE pattern;

Case Sensitivity:

The LIKE operator’s behavior with case sensitivity depends on the collation setting of the database. In some databases, it’s case-insensitive, while in others, it’s case-sensitive.

Use of Wildcards % and _

Wildcards are special characters used with the LIKE operator to define the pattern you’re searching for:

• Percent Sign (%):
  • The % wildcard represents zero, one, or multiple characters.
  • Example: LIKE 'a%' finds any values that start with “a”.
• Underscore (_):
  • The _ wildcard represents a single character.
  • Example: LIKE 'a_' finds any values that start with “a” and are followed by exactly one more character.

Practice Exercises

1. Select all customers whose names start with ‘J’ from the customers table.
2. Find products whose names end with ‘e’ from the products table.
3. Retrieve all customers whose names are five characters long in the customers table.
4. List products with a three-letter name from the products table.
5. Find any customers in the customers table whose names start with ‘J’ and have at least one more character.
6. Search for products in the products table that start with any character, followed by ‘a’, and then any characters.