LIKE and ILIKE are used for pattern matching in PostgreSQL. LIKE is the SQL standard while ILIKE is a useful extension made by PostgreSQL.
To begin with, we will create a tiny table with few random string values.
CREATE TABLE string_collection ( string character varying )
Two of the important selectors in pattern matching with LIKE/ILIKE are the percentage sign(%) and underscore(_).
% sign in a pattern matches any sequence of zero or more characters.
_ in a pattern matches any single character.
For instance, consider the following query:
SELECT string FROM string_collection WHERE string LIKE 'O%';
It matches all strings beginning with ‘O’ .
Following query illustrate the use of _:
SELECT string FROM string_collection WHERE string LIKE '_n_';
In plain English, “It matches all strings of three characters whose middle character is small letter n” .
It is also possible to combine _ and % to get the desired result:
SELECT string FROM string_collection WHERE string LIKE '_n%';
ILIKE is similar to LIKE in all aspects except in one thing: it performs a case in-sensitive matching:
SELECT string FROM string_collection WHERE string ILIKE 'O%';
The same effect can be achieved using the ‘lower()’ function and LIKE of PostgreSQL.
SELECT string FROM string_collection WHERE lower(string) LIKE ‘o%’;
A performance comparison of LIKE with lower vs ILIKE can be read here.
LIKE and ILIKE can be preceded with NOT to get the reverse effect:
SELECT string FROM string_collection WHERE string NOT LIKE 'O%';
A % without any other character matches all strings:
SELECT string FROM string_collection WHERE string LIKE '%';
Now an important question arises: How to match a % or _ itself?
To do so, we have to escape them using the default escape character – backslash.
For example:
SELECT string FROM string_collection WHERE string LIKE '%\%';
This query is equivalent to: “Match all strings which ends with a % sign”.
Underscores can also be mapped in the same way:
SELECT string FROM string_collection WHERE string LIKE '%\_%';
It matches any strings which contain an underscore.
~~ can be used instead of LIKE and also:
~~* for ILIKE
!~~ for NOT LIKE
!~~* for NOT ILIKE
Indexing and LIKE/LIKE
Indexes are used to fasten the search. PostgreSQL automatically create indexes for columns which are Primary Keys, Unique, etc. Or we can create indexes explicitly.
If an Index is available for the column, the LIKE utilizes it, if the pattern doesn’t start with % or _.
So, col LIKE ‘one%’ use index, while col LIKE ‘%one’ does not.
ILIKE make use of index if and only if the pattern start with a non-alphabetic character(character not affected by case conversions).
Bonus Point: Mirrored Indexes
As we discussed LIKE would not use Index to search if the pattern start with % or _.
A work around for this is following:
- Create a index on the column using the reverse() function.
- Search with reversed pattern.
For example, the following query doesn’t use indexes:
SELECT string FROM string_collection WHERE string LIKE '%wo';
Now, we create an index:
CREATE INDEX rev_idx ON string_collection (reverse(string));
And rewrite our query:
SELECT string FROM string_collection WHERE reverse(string) LIKE reverse('%wo');
Now it will use index.
That’s it. Thank You!
