SQL ILIKE

The ILIKE operator is very similar to the LIKE operator in that both are for pattern matching. There are two main differences between the two:

• ILIKE is case-insensitive whereas LIKE is case-sensitive.
• LIKE is a standard SQL operator, where as ILIKE is only implemented in certain databases such as PostgreSQL and Snowflake.

Syntax

The syntax for the ILIKE operator is as follows:

SELECT "column_name"
FROM "table_name"
WHERE "column_name" ILIKE {PATTERN};

{PATTERN} often consists of wildcards. We saw several examples of wildcard matching in a previous section.

Example

We use the following table for our example.

Table Store_Information

We want to find all stores whose name ends in 'O' or 'o.' To do so, we key in,

SELECT *
FROM Store_Information
WHERE Store_Name ILIKE '%o';

Result:

The "%" sign before 'o' means that there may be 0, 1, or more characters before the pattern we want to match. The use of ILIKE means that the last character of the store name could either be 'O' or 'o.' Out of the four store names, 'SAN DIEGO' and 'SAN FRANCISCO' both satisfy this pattern.