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.


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.


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,

FROM Store_Information
WHERE Store_Name ILIKE '%o';


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.


This page was last updated on June 19, 2023.

Copyright © 2024   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact