PostgreSQL LIKE

PostgreSQL LIKE Operator

The LIKE operator is utilized heavily in a WHERE clause to perform pattern matching. It allows you to search for a specific sequence of characters in a text column.

Instead of requiring an exact, identical match, it performs a fuzzy search. This is how basic search bars on standard websites generally function.

The Wildcard Characters

To build a search pattern, LIKE utilizes two primary wildcard characters. The percent sign (%) represents zero, one, or multiple undetermined characters.

The underscore sign (_) rigorously represents exactly one single undetermined character. You can combine these wildcards in endless variations to find precise strings.

-- Find any customer whose name definitively starts with the letter 'a'
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';

Common LIKE Patterns

Understanding how to position the % wildcard is critical for good search logic. 'a%' finds any values that aggressively start with the letter "a".

'%a' forcefully finds any values that end with the letter "a". '%or%' finds any values that have "or" located in absolutely any position.

-- Find customers whose names contain the sequence 'an' anywhere
SELECT * FROM Customers
WHERE CustomerName LIKE '%an%';

The Underscore Wildcard

The _ wildcard is much more specific than the percent sign. It mandates that a character must exist in that exact sequential spot.

'_r%' finds any value that has "r" specifically in the second position. 'a__%' finds values starting with "a" that are at least 3 characters long.

-- Find customers whose names have 'r' in the second position
SELECT * FROM Customers
WHERE CustomerName LIKE '_r%';

Case Sensitivity (ILIKE)

By default, the standard LIKE operator in PostgreSQL is strictly case-sensitive. Searching for 'A%' will not return names that start with a lowercase 'a'.

To perform a case-insensitive search seamlessly, PostgreSQL provides ILIKE. Using ILIKE makes search queries much more robust for user-generated inputs.

Summary

The LIKE operator enables highly dynamic, partial string searching. Use the % character to match bulky segments, and _ for strict spacing.

Always prefer the ILIKE operator when building user-facing search features.