LIKE and ILIKE Statement in SQL

The LIKE and ILIKE clauses in SQL are used for pattern matching in string data, enabling queries to find matches based on specific patterns within strings. The LIKE operator is case-sensitive, while ILIKE, available in some SQL dialects such as PostgreSQL, performs a case-insensitive match.

Introduction to LIKE and ILIKE

These operators are particularly useful in scenarios where precise matching is not feasible, such as searching user-generated content, filtering names in a customer database, or querying descriptions in a catalog. By using wildcard characters, such as % (which matches any sequence of characters) and _ (which matches any single character), these operators can significantly enhance the flexibility of search functionalities in SQL queries.

Scenario for Example

Suppose you have a table named Books with a column Title that contains book titles. You want to find all books that contain the word “guide” in their title, regardless of case, and also books that start with “The”.

SQL Schema for Books Table

• Books
  • BookID (int)
  • Title (varchar)

SQL Query Using LIKE and ILIKE

Here’s an example of using both LIKE and ILIKE in a query:

-- This example assumes the use of PostgreSQL for the ILIKE operator
SELECT
    BookID,
    Title
FROM
    Books
WHERE
    Title ILIKE '%guide%'  -- Case-insensitive search for any titles containing "guide"
OR
    Title LIKE 'The%'      -- Case-sensitive search for titles starting with "The"
ORDER BY
    Title;

Explanation of the Code

1. Column Selection:
   • BookID and Title: Selects the ID and the title of the book from the Books table.
2. WHERE Clause:
   • Title ILIKE '%guide%': Searches for books where the title contains the word “guide” in any case. The % wildcards mean that “guide” can appear anywhere in the title.
   • Title LIKE 'The%': Looks for books where the title starts with “The”. Here, the % wildcard allows for any characters to follow after “The”.
3. ORDER BY Clause:
   • Orders the results by the Title to make the output easy to browse.

Example Output

Assuming the Books table contains the following entries:

• BookID: 1, Title: “Guide to SQL”
• BookID: 2, Title: “The Great Outdoors”
• BookID: 3, Title: “A Complete Guide to Cooking”
• BookID: 4, Title: “Thematic Elements of Design”
• BookID: 5, Title: “the quick guide”

The output of the query would be:

Output Explanation

• The books with IDs 1, 3, and 5 appear because they contain the word “guide”, matching the ILIKE condition.
• The book with ID 2 appears because its title starts with “The”, satisfying the LIKE condition.
• BookID 4 does not appear because it neither starts with “The” in a case-sensitive manner nor contains “guide”.