SQL ILIKE

SQL > SQL Commands > 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

Store_Name	Sales	Txn_Date
LOS ANGELES	1500	Jan-05-1999
SAN DIEGO	250	Jan-07-1999
SAN FRANCISCO	300	Jan-08-1999
BOSTON	700	Jan-08-1999

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:

Store_Name	Sales	Txn_Date
SAN DIEGO	250	Jan-07-1999
SAN FRANCISCO	300	Jan-08-1999

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.

Next: SQL ORDER BY

This page was last updated on June 19, 2023.

AdBlock Detected!

Our website is made possible by displaying ads to our visitors. Please supporting us by whitelisting our website.