Posted on  by  admin


If you’ve done any pattern matching in your PostgreSQL queries, you’ve probably become acquainted with the LIKE operator. However, you might not yet be familiar with its counterpart: the ILIKE operator. For the most part, this operator behaves in a similar manner to the LIKE operator; what makes it unique is that it allows for case-insensitive pattern matching. In this article, we’ll take a closer look at the ILIKE Postgres operator and check out some examples of how it can be used in queries.

  1. Ilike organic skin care Questions about ilike organic skin care products will be happily answered by our knowledgeable skincare specialists; contact us!. Click HERE to see list of brands typically excluded from store promos.
  2. 4.4 out of 5 stars. Ilike grape stem cell solutions moisturizer - 1.7 oz. 4.5 out of 5 stars. Ilike fibrous stonecrop gel mask - 1.7 fl oz. 5.0 out of 5 stars.
  3. Hyaluronic Elixir. Quench your skin’s thirst with this clinically proven elixir! Benefiting all skin types, this oil-free, organic hyaluronic acid booster serum plumps the skin from the inside out, promotes collagen production.


Before diving into the examples in this tutorial, take a moment to review the prerequisites for the task:

  • Be sure to have PostgreSQL installed on your computer so that you can follow along with our instructions and examples.
  • It’s helpful to have at least some knowledge of PostgreSQL in order to get the most out of this tutorial.

PostgreSQL ILIKE Operator Syntax

As we mentioned earlier, the ILIKE operator is used in the same way as the LIKE operator. The difference is that ILIKE allows you to perform case-insensitive pattern matching. Shown below is the basic syntax for this operator:

WHERE column_name ILIKE 'a%'

In the syntax example above, we search for the values that start with “a”:

This example searches for the values that end with “a”:

WHERE column_name ILIKE '%a%'

Ilike Organic Skin Care. March 26th is NATIONAL SPINACH DAY. Spinach is a rich source of iron, as well as vitamin A, vitamin C, vitamin E, vitamin K, magnesium, and several vital antioxidants. Recently, opioid peptides called rubiscolins have also been found in spinach.

We can even search for the values that contain “a” in any position:

The above example looks for values that contain “a” in the second position:

WHERE column_name ILIKE 'a%b'

Our final syntax example in this section searches for the values that start with “a” and end with “b”.

Create a PostgreSQL database and table

Before we can show you some real-life examples of queries using ILIKE in Postgres, we’l need to set up a database and table. If you already have these available for use, you can skip this section; otherwise, follow along with the steps listed below.

To create a database in PostrgreSQL, we use the following statement:

  • We can use the command c to connect our database once it’s created:

To create a table in PostgreSQL, we’ll use this statement:

name TEXT,
gender VARCHAR(6)

Shown below is the contents of our table. You can populate your table with the same records or use different data if you prefer:

id name gender
1 Paulina Bright Female
2 Eliza Herring Female
3 Darren Ferguson Male
4 Artur Cisneros Male
5 Brittany Hodges Female
6 Joe Green Male
7 George Welch Male
8 Anthony Mann Male
9 Michele Morgan Female
10 Alycia Seaton Female
(10 rows)

PostgreSQL ILIKE Examples

Searching using ILIKE

In our first example using our student table, we will search for names that start with “a”. We will use the following statement:

SELECT*FROM student WHERE name ILIKE 'a%';
id name gender
4 Artur Cisneros Male
8 Anthony Mann Male
10 Alycia Seaton Female

You can see that the records in the result do contain names that match the query conditions.


Searching for specific character position

Our next example will return records that have “i” as the third character of the string:

SELECT*FROM student WHERE name ILIKE '__i%';
id name gender
2 Eliza Herring Female
5 Brittany Hodges Female


Let’s look at an example that searches for records that do not contain the letter “o”. To do this, we’ll use the NOT ILIKE expression as seen in the following statement:

id name gender
1 Paulina Bright Female
2 Eliza Herring Female

Searching for the after-space character

If the name field contains both first and last names together, how could we search for the first character of a student’s last name? We could use the ILIKE operator to locate the first character that comes after the space that separates first and last name:

SELECT*FROM student WHERE name ILIKE '% G%';
id name gender
6 Joe Green Male

In this example, we search for the letter “r” in any position in the last name:

SELECT*FROM student WHERE name ILIKE '% %r%';
id name gender
1 Paulina Bright Female
2 Eliza Herring Female
3 Darren Ferguson Male
4 Artur Cisneros Male
6 Joe Green Male
9 Michele Morgan Female


I Like You

When you need to perform pattern matching in PostgreSQL but also need to ignore case, the ILIKE operator provides the functionality you need. In this article, we discussed the ILIKE Postgres operator and showed many examples of its use. If you’ve followed along with this tutorial, you’ll be prepared to incorporate the ILIKE operator into your own PostgreSQL queries.


String & Binary Functions (Matching/Comparison)

Allows matching of strings based on comparison with a pattern. Unlike the LIKE function, string matching is case-insensitive.

LIKE, ILIKE, and RLIKE all perform similar operations; however, RLIKE uses POSIX EXE (Extended Regular Expression) syntax instead of the SQL pattern syntax usedby LIKE and ILIKE.

See also






Subject to match.


Pattern to match.



Character(s) inserted in front of a wildcard character to indicate that the wildcard shouldbe interpreted as a regular character and not as a wildcard.

Usage Notes¶

  • SQL wildcards are supported in pattern:

    • An underscore (_) matches any single character.

    • A percent sign (%) matches any sequence of zero or more characters.

  • Wildcards in pattern include newline characters (n) in subject as matches.

  • ILIKE pattern matching covers the entire string. To match a sequence anywhere within a string, start and end the pattern with %.

  • There is no default escape character.

  • If you use the backslash as an escape character, then you must specify escape the backslash in theESCAPE clause. For example, the following command specifies that the escape character is the backslash, and thenuses that escape character to search for ‘%’ as a literal (without the escape character, the ‘%’ would be treatedas a wildcard):

    For examples of using escape characters, and in particular the backslash as an escape character, seethe examples for the LIKE function.

Collation Details¶

Arguments with collation specifications are currently not supported.

Coments are closed

Recent News

  • PhotoStickies
  • TunesKit IBook Copy For Mac
  • Tracking
  • Reeder

Scroll to top