PostgreSQL LIKE operator

In PostgreSQL, the LIKE operator is used to match text values against a pattern using wildcard characters and filter out data based on that. If a match occurs, the LIKE operator returns TRUE. The LIKE operator does a case-sensitive search.

The LIKE operator can be used in the SELECT and WHERE clause of DML queries like SELECT, INSERT, UPDATE or DELETE.

There are two wildcard characters that can be used with the LIKE operator

% represents zero, one or more characters or numbers.
_ represents one character or number.

Syntax of LIKE operator is <value> LIKE <pattern>

The above expression returns TRUE if specified <value> matches with the <pattern>.

To fetch the non-matching data, the NOT LIKE operator can be used. The syntax is <value> NOT LIKE <pattern>

The following example demonstrates the LIKE operator:

Example: LIKE Patterns Matching

SELECT
	'postgre' LIKE 'postgre', -- true
	'postgre' LIKE 'Postgre', -- false
	'postgre' LIKE 'post%', -- true
	'postgre' LIKE '_ostgr_', -- true
	'postgre' LIKE 'post_'; -- false

In the above example:

The first expression returns true as pattern 'postgre' does not have any wildcard characters so the LIKE operator behaves like the equal-to (=) operator.
The second expression returns false as pattern 'Postgre' is not equal to value 'postgre'. The LIKE operator considers case sensitivity while comparing a value to a pattern.
The third expression returns true as it matches any string starting with 'post' and followed by any number of characters or numbers.
The fourth expression returns true as pattern '_ostgr_' matches any string that begins with a single character followed by 'ostgr' and ended with a single character.
The fifth expression returns false as pattern 'post_' expects only a character followed by a post, which is not true for value 'postgresql'.

The following query will return all employees that contain 'ay' string in their first name like 'May', 'Hayward', etc.

Example: LIKE Pattern

SELECT * FROM Employee WHERE first_name LIKE '%ay%';

Here the pattern is using percentage (%) and underscore (_) both wildcard characters. The following query will return all employees whose first name has any number of characters followed by 'ry' and ended with one character.

Example: LIKE Pattern

SELECT * FROM Employee WHERE first_name LIKE '%ry_';

PostgreSQL supports the NOT LIKE operator to find employees whose name does not contain 'ay' in first_name.

Example: LIKE Pattern

SELECT * FROM Employee WHERE first_name NOT LIKE '%ay%';

ILIKE Operator

PostgreSQL supports the ILIKE operator for case-insensitive pattern matching. It is not SQL standard but it is a PostgreSQL extension.

Example: ILIKE

SELECT
	'postgre' LIKE 'Postgre', -- false
	'postgre' ILIKE 'Postgre', -- true
	'postgre' ILIKE 'POST%', -- true
	'postgre' ILIKE '_OStgr_'; -- true

In the above query, the first expression uses the LIKE operator and returns false, while all other expressions are using ILIKE operator that does case–insensitive search. Note that the ILIKE operator can also use one or more wildcard characters for searching string.