The complete list of PostgreSQL functions and operators can be found here↗.
LIKE and ILIKE
Sometimes you want to search for something in your database but it's not an exact match.
LIKE and ILIKE are for very limited fuzzy matching of text. They are not doing things like dropping "stop words" (like and, the, with, etc.) or handling plurals, or handling similar spellings (like color vs colour).
SELECT * FROM ingredients
WHERE title LIKE '%pota%';You would expect a search for "pota" to match "potato".
ILIKE does the same thing, just with case insensitivity.
Character matching
Use in combination with LIKE and ILIKE, the symbols % and _ are for character matching.
% matches 0 to infinite characters.
_ will match exactly 1 character.
Placement of these symbols matters.
So with "%berry" you would match "strawberry" and "blueberry" but not "berry ice cream". That's because the % was only at the beginning so it wouldn't match anything after "berry".
For that you'd need "%berry%" to match both "strawberry" and "blueberry ice cream".
You can put % anywhere. "b%t" will match "bt", "bot", "but", "belt", and "belligerent".
Remember that _ will match 1 and only one character. "b_t" will match "bot" and "but" but not "bt", "belt", or "belligerent".
CONCAT
CONCAT() is a function that can take strings and combine them together.
We can concat our title and type columns and then use ILIKE on the results of that combined string.
SELECT * FROM ingredients
WHERE CONCAT(title, type) ILIKE '%fruit%';From the Complete Intro to SQL & PostgreSQL↗ course on FEM↗ taught by Brian Holt↗.