You can use the LIKE operator in PostgreSQL to select rows where a string matches a pattern.
By default, the LIKE operator is case-sensitive.
However, there are two ways to use a case-insensitive LIKE operator instead:
Method 1: Use ILIKE
SELECT *
FROM athletes
WHERE role ILIKE '%Guard%';
This particular example will select all rows from the table named athletes where the string in the role column contains the pattern ‘Guard’, regardless of case.
Method 2: Use LOWER
SELECT *
FROM athletes
WHERE LOWER(role) LIKE LOWER('%Guard%');
This particular example will also select all rows from the table named athletes where the string in the role column contains the pattern ‘Guard’, regardless of case.
Note that this method uses the LOWER command to first convert all strings in the role column to lowercase, then uses the LOWER command again to convert the search pattern to lowercase.
Note: The ILIKE operator offers easier syntax but it has been shown that if your table is extremely large then the ILIKE operator tends to perform much slower than the LOWER method.
The following example shows how to use each method in practice.
Example: How to Use Case-Insensitive LIKE Operator in PostgreSQL
Suppose that we create the following table named athletes that contains information about various basketball players:
-- create table named 'athletes'
CREATE TABLE athletes (
id INTEGER PRIMARY KEY,
team TEXT,
role TEXT
);
-- add rows to the table
INSERT INTO athletes VALUES (0001, 'Mavs', 'Shooting Guard');
INSERT INTO athletes VALUES (0002, 'Hawks', 'Point guard');
INSERT INTO athletes VALUES (0003, 'Rockets', 'Center');
INSERT INTO athletes VALUES (0004, 'Spurs', 'Point Guard');
INSERT INTO athletes VALUES (0005, 'Kings', 'Power Forward');
INSERT INTO athletes VALUES (0006, 'Celtics', 'Shooting GUARD');
INSERT INTO athletes VALUES (0007, 'Lakers', 'Center');
-- view all rows in table
SELECT * FROM athletes;
id | team | role
----+---------+----------------
1 | Mavs | Shooting Guard
2 | Hawks | Point guard
3 | Rockets | Center
4 | Spurs | Point Guard
5 | Kings | Power Forward
6 | Celtics | Shooting GUARD
7 | Lakers | Center
Suppose that we would like to only select the rows where the string in the role column contains the pattern ‘Guard’, regardless of case.
One way to do this is by using the ILIKE operator as follows:
SELECT *
FROM athletes
WHERE role ILIKE '%Guard%';
id | team | role
----+---------+----------------
1 | Mavs | Shooting Guard
2 | Hawks | Point guard
4 | Spurs | Point Guard
6 | Celtics | Shooting GUARD
Another way to do so is by using the LOWER command as follows:
SELECT *
FROM athletes
WHERE LOWER(role) LIKE LOWER('%Guard%');
id | team | role
----+---------+----------------
1 | Mavs | Shooting Guard
2 | Hawks | Point guard
4 | Spurs | Point Guard
6 | Celtics | Shooting GUARD
Notice that both methods return all rows from the table where the string in the role column contains ‘Guard’, regardless of case.
Feel free to use whichever method you prefer, depending on the size of your table.
Additional Resources
The following tutorials explain how to perform other common tasks in PostgreSQL:
PostgreSQL: How to Use LIKE Operator with Multiple Values
PostgreSQL: How to Find Position of Character in String
PostgreSQL: Replace All Occurrences of String in Text Field
