How to select rows where a text column starts by, ends by, or contains a string. Including slightly less trivial cases.
LIKE
To find strings that match a pattern, the LIKE operator is used.
LIKE as equal
In the most trivial case, LIKE is identical to the = operator. These two queries are the same:
SELECT id FROM book WHERE title = 'Don Quijote';
SELECT id FROM book WHERE title LIKE 'Don Quijote';Any sequence of characters
LIKE treats % as a wildcard character, which means: any sequence of zero or more characters. So the following conditions look for strings that start by, end by, or contain the given strings.
-- start
... WHERE title LIKE '2001%';
-- contain
... WHERE title LIKE '%space%';
-- end
... WHERE title LIKE '%odissey';Any single character
Something to remember is that, as mentioned above, % may also mean no characters at all. So, in the first example above, the title '2001' would match. This may not be what we want.
Another special character recognised by LIKE is _, which means exactly one character. So, for example, the following condition will match any text with at least one character or, if you like, any non-empty text:
WHERE title LIKE '_%';This is the same as WHERE CHAR_LENGTH(title) > 0 but, since this syntax is more verbose and slightly varies depending on the DBMS, the LIKE version may be preferable.
We can also modify the examples above to avoid matching exact words:
-- start
... WHERE title LIKE '2001_%';
-- contain
... WHERE title LIKE '%space_%' OR title LIKE '%_space%';
-- end
... WHERE title LIKE '%_odissey';Multiple searches
We can also check if a column contains more than one given texts. The obvious way is the following:
... WHERE title LIKE '%star%' AND title LIKE '%picard%';But if we know in which order the texts should appear, the following condition is less verbose and probably faster (though this may depend if the DBMS implements certain optimisations in the first case or not):
... WHERE title LIKE '%star%picard%';Indexes
Generally speaking, indexes can only be used for conditions that look for a text at the beginning of a column:
... WHERE title LIKE 'star%';In this simple case, for the index to be used, it needs to only include the title column or the title column needs to be the first column in the index.
In a real world query there are useful more conditions, and many indexes contain multiple columns. But explaining what happens in more complex cases is beyond the scope of this article.
PostgreSQL: ILIKE
In PostgreSQL LIKE is case-sensitive, so it also it has an ILIKE operator which is case-insensitive:
SELECT id FROM book WHERE title ILIKE 'don quijote';