Postgresql ilike case insensitive

In this PostgreSQL tutorial, we will learn about “Postgresql ilike case insensitive” which is similar to the behaviour 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 that will contain the name and gender of persons in the United States of America (USA).

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');

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%';

In the above code, we are fetching all columns from a table person_data using the SELECT statement. Then filtering it using the WHERE clause with the LIKE operator, this LIKE operator filters the specific name of the person that starts with ‘a’ only.

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

SELECT * FROM person_data WHERE name ILIKE '%s';

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

SELECT * FROM person_data WHERE name ILIKE '%e%';

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%';

Here in the above code, fetching the person name from a table person_data using the SELECT statement, then filtering the name of the person that contains the second character as ‘o’ using the LIKE operator in WHERE clause. here underscore(_) after the LIKE operator with a single quote represent the single character.

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

SELECT * FROM person_data WHERE name ILIKE '%e_';

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%';

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

SELECT * FROM person_data WHERE name NOT ILIKE '___e%';

So here in the above code fetching the name of the person from person_data using the SELECT statement, and filtering it using the WHERE clause with NOT ILIKE operator.

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%';

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

SELECT * FROM person_data WHERE name ILIKE '% a. %';

You may also like to read the following PostgreSQL tutorials.

• Postgresql row_number
• Postgresql auto increment
• Postgresql date comparison
• Postgresql create database
• PostgreSQL CREATE INDEX
• Postgresql date to string
• Postgresql import SQL file
• Postgresql set user password
• Postgresql generate_series

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