Postgresql ilike case insensitive

In this PostgreSQL tutorial, we will learn about “Postgresql ilike case insensitive” which is similar to the behavior of the LIKE operator, but ILIKE is unique because it is used for case-insensitive pattern matching.

There are the following topics that we will cover in this tutorial.

  • Searching with ILIKE
  • Searching after a specific character
  • Search using NOT ILIKE
  • Searching after-space character

Syntax:

In the syntax below, we use to search the value that starts with “a”.

WHERE col_name ILIKE 'a%'

This syntax searches for the value that ends with “a”.

WHERE col_name ILIKE '%a'

We can also search for the value that contains “a” in any position.

WHERE col_name ILIKE '%a%'

The below syntax looks for the values “a” after a character or at the second position.

WHERE col_name ILIKE '_a%'

In the above syntax underscore (“_”) represents a character.

The last syntax looks for the values that start with “a” and end with “x”.

WHERE col_name ILIKE 'a%x'

Before beginning, first, we will create the database named demo_data in Postgresql and insert some data into it, then we are going to understand with some examples.

To create a database in Postgresql, use the below statement.

CREATE DATABASE demo_data;

Now create the table named person_data.

CREATE TABLE person_data(
    id SERIAL PRIMARY KEY,
    name TEXT,
    gender VARCHAR(20)
);

Insert the following records.

INSERT INTO person_data(name,gender)VALUES('Jose A. Wheeler','MALE'),
('William M. Roy','MALE'),('Donald N. Boles','MALE'),('Corey G. Miller','MALE'),
('Timothy A. Hanna','FEMALE'),
('Amanda R. Cranmer','FEMALE'),('Earline R. Williams','FEMALE'),
('Anthony Mann','MALE'),('Alycia Seato','FEMALE'),
('Eliza Herrin','FEMALE');
Postgresql ilike case insensitive
Postgresql ilike case insensitive

Read: Postgresql group_concat

Searching with ILIKE

In our first example of Postgresql, we are going to use the table named person_data and search for the name that starts with “a”.

SELECT * FROM person_data WHERE name ILIKE 'a%';
Postgresql ilike case insensitive
Postgresql ilike case insensitive

Search for the person’s name that ends with “s”.

SELECT * FROM person_data WHERE name ILIKE '%s';
Postgresql ilike case insensitive
Postgresql ilike case insensitive

Look for the person’s name that contains “e”.

SELECT * FROM person_data WHERE name ILIKE '%e%';
Postgresql ilike case insensitive

Read: How to migrate from MySQL to Postgres

Searching after a specific character

In Postgresql, we can search for the person’s name for a specific character position using the ILIKE operator.

Let’s run the below statement to know the name of the person whose second character is “o”.

SELECT * FROM person_data WHERE name ILIKE '_o%';
Postgresql ilike case insensitive
Postgresql ilike case insensitive

Search for the name that contains “e” before the last character.

SELECT * FROM person_data WHERE name ILIKE '%e_';
Postgresql ilike case insensitive
Postgresql ilike case insensitive

Read: Postgresql listen_addresses

Search using NOT ILIKE

In Postgresql, we can use the NOT operator with ILIKE to show results opposite of what we search.

Now search the name of the person that doesn’t contain “e” at any position.

SELECT * FROM person_data WHERE name NOT ILIKE '%e%';
Postgresql ilike case insensitive
Postgresql ilike case insensitive

look for the name of the person whose third character is not “e”.

SELECT * FROM person_data WHERE name NOT ILIKE '___e%';
Postgresql ilike case insensitive
Postgresql ilike case insensitive

Read: Postgresql current_timestamp

Searching after-space character

In Postgresql, we are going to search after the space character and it will show the name that contains space.

As we know the full name is consists of first and last names and how can we search for the first character of a person’s last name?.

Let’s understand with an example.

SELECT * FROM person_data WHERE name ILIKE '% h%';
Postgresql ilike case insensitive
Postgresql ilike case insensitive

Search for the names whose first name ends with “a.” before the last name using space.

SELECT * FROM person_data WHERE name ILIKE '% a. %';
Postgresql ilike case insensitive
Postgresql ilike case insensitive

You may also like to read the following PostgreSQL tutorials.

So, in this Postgresql tutorial, we have learned about “Postgresql ilike case insensitive” and covered the following topics.

  • Searching with ILIKE
  • Searching after a specific character
  • Search using NOT ILIKE
  • Searching after-space character

Leave a Comment