PostgreSQL: Using the LIKE Condition for Pattern Matching

Filter data in PostgreSQL based on patterns using the LIKE condition. This guide explains wildcard characters and case sensitivity.

PostgreSQL LIKE Condition

Introduction to the PostgreSQL LIKE Condition

The PostgreSQL LIKE condition retrieves data from a table based on pattern matching. The results are case-sensitive and depend on the specified pattern.

Wildcards

PostgreSQL uses two wildcard characters for pattern matching:

• % (percent): Matches any sequence of zero or more characters.
• _ (underscore): Matches any single character.

PostgreSQL LIKE Condition Syntax

The basic syntax, used with the WHERE clause and various commands (SELECT, INSERT, UPDATE, DELETE), is:

expression LIKE pattern [ ESCAPE 'escape_character' ]
            

This returns true if the expression matches the pattern.

The NOT LIKE operator does the opposite:

expression NOT LIKE pattern [ ESCAPE 'escape_character' ]
            

This returns true if the expression does *not* match the pattern.

Parameters

Note: If the pattern has no wildcards, LIKE behaves like the = operator.

Examples

Example 1: Using the Percent (%) Wildcard

Find employees whose first name starts with 'Kat':

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname LIKE 'Kat%';
            

'Kat%' matches any string starting with 'Kat' followed by any characters.

Example 2: Pattern Matching

Some pattern matching examples:

• 'abc' LIKE 'abc' - True (no wildcards)
• 'abc' LIKE 'a_' - False (matches 'a' followed by one character)
• 'abc' LIKE '_b_' - True (matches one character, 'b', one character)
• 'abc' LIKE 'a%' - True (matches 'a' followed by any characters)
• SELECT emp_fname, emp_lname FROM employee WHERE emp_fname LIKE '%in%' - Finds names containing "in" anywhere.

Example 3: Using the Underscore (_) Wildcard

Find employees whose last name starts with any character followed by "mith":

SELECT emp_fname, emp_lname, location
FROM employee
WHERE emp_lname LIKE '_mith';
            

Example 4: Combining Wildcards

Find employees whose first name starts with any character, followed by "at", and then any other characters:

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname LIKE '_at%'
ORDER BY emp_fname;
            

Example 5: NOT LIKE Operator

Find employees whose first name does *not* start with 'Kat':

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname NOT LIKE 'Kat%';
            

ILIKE Operator

The ILIKE operator is case-insensitive:

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname ILIKE 'ka%';
            

This will match 'Ka%', 'kA%', 'ka%', 'KA%', etc.

Other Operators

PostgreSQL also provides these similar operators:

Overview

This section covered PostgreSQL's LIKE and related operators for pattern matching, including wildcards and case-sensitivity.