LIKE and Wildcards in SQL

The LIKE operator in SQL is used in conjunction with wildcards to search for a specified pattern in a column. It is often used in WHERE clauses to filter data based on partial matches, rather than requiring an exact match. This makes LIKE a powerful tool for flexible searching.

1. Basic Syntax of LIKE

The basic syntax of the LIKE operator involves using it with a string pattern. The pattern can include wildcards to represent one or more unknown characters.

Syntax:

      SELECT column_name
      FROM table_name
      WHERE column_name LIKE pattern;
    

In this syntax, column_name refers to the column you want to filter, and pattern is the string pattern you are looking for. The pattern can include wildcards, which will be explained below.

2. Wildcards in SQL

SQL supports two main wildcards when used with the LIKE operator:

• %: Represents zero or more characters.
• _: Represents a single character.

These wildcards allow you to perform more flexible searches by matching patterns of varying lengths or positions in the column values.

Example: Using % Wildcard

The % wildcard matches any sequence of characters (including an empty sequence). It can be placed at the beginning, middle, or end of the pattern.

Example 1: Pattern Matching with % at the End

      -- Find all employees whose names start with 'J'
      SELECT FirstName
      FROM Employees
      WHERE FirstName LIKE 'J%';
    

This query returns all employees whose first names start with the letter 'J'. The % wildcard at the end of 'J%' means any characters can follow 'J'.

Example 2: Pattern Matching with % at the Beginning

      -- Find all employees whose names end with 'son'
      SELECT FirstName
      FROM Employees
      WHERE FirstName LIKE '%son';
    

This query retrieves all employees whose first names end with 'son'. The % at the beginning of the pattern means any characters can precede 'son'.

Example 3: Pattern Matching with % in the Middle

      -- Find all employees whose names contain 'an' anywhere
      SELECT FirstName
      FROM Employees
      WHERE FirstName LIKE '%an%';
    

This query returns all employees whose first names contain the substring 'an' anywhere in the name.

Example: Using _ Wildcard

The _ wildcard represents a single character. It can be used to match any single character at a specific position within the pattern.

Example 1: Pattern Matching with _ for Single Character

      -- Find all employees whose names have 'a' in the second position
      SELECT FirstName
      FROM Employees
      WHERE FirstName LIKE '_a%';
    

This query retrieves all employees whose first names have the letter 'a' as the second character. The _ wildcard matches any single character before 'a', and the % matches any characters after it.

Example 2: Pattern Matching with _ for Specific Character Length

      -- Find all employees whose names have exactly 4 characters, with 'a' as the second character
      SELECT FirstName
      FROM Employees
      WHERE FirstName LIKE '_a__';
    

This query finds all employees whose first names consist of exactly 4 characters, with 'a' as the second character. The _ wildcard represents each character position, and the pattern '_a__' specifies that there should be exactly 3 characters, with 'a' in the second position.

3. Combining LIKE with Other Operators

The LIKE operator can be used in combination with other SQL operators to build more complex queries. You can use it with AND, OR, and NOT to filter data further.

Example: Combining LIKE with AND

      -- Find employees whose names start with 'J' and have 'a' as the second letter
      SELECT FirstName
      FROM Employees
      WHERE FirstName LIKE 'J%' AND FirstName LIKE '_a%';
    

This query retrieves all employees whose names start with 'J' and have 'a' as the second letter. It combines two LIKE conditions using the AND operator.

Example: Using NOT LIKE

      -- Find employees whose names do not start with 'J'
      SELECT FirstName
      FROM Employees
      WHERE FirstName NOT LIKE 'J%';
    

This query retrieves all employees whose first names do not start with the letter 'J'. The NOT LIKE condition is used to exclude rows that match the pattern.

4. Case Sensitivity in LIKE

In most SQL databases, the LIKE operator is case-insensitive by default. However, the behavior can vary depending on the database system and the collation settings. Some databases may be case-sensitive unless specified otherwise.

Example: Case Insensitive LIKE

      -- Find all employees whose names start with 'j' or 'J'
      SELECT FirstName
      FROM Employees
      WHERE FirstName LIKE 'j%';
    

This query retrieves all employees whose first names start with 'j' or 'J'. The LIKE operator is typically case-insensitive in many databases, so both lowercase and uppercase 'j' will match.

Example: Case-Sensitive LIKE in MySQL

      -- Find all employees whose names start with 'j' (case-sensitive in MySQL)
      SELECT FirstName
      FROM Employees
      WHERE FirstName LIKE BINARY 'j%';
    

This query performs a case-sensitive search for employees whose names start with lowercase 'j'. The BINARY keyword forces the LIKE operator to be case-sensitive in MySQL.

5. Conclusion

The LIKE operator and wildcards are essential tools in SQL for performing flexible pattern matching. By using % and _ wildcards, you can easily search for partial matches and manipulate strings in a variety of ways. Understanding how to use LIKE and wildcards in SQL will enable you to write more powerful and efficient queries for data retrieval.