> > > LIKE and ILIKE for Pattern Matching in PostgreSQL

LIKE and ILIKE for Pattern Matching in PostgreSQL

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
)

/home/vipin/Pictures/PostgreSQL-LIKE-ILIKE-Pattern-Performance_010.pngTwo 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%';

/home/vipin/Pictures/PostgreSQL-LIKE-ILIKE-Pattern-Performance_011.pngIt matches all strings beginning with ‘O’ .

Following query illustrate the use of _:

SELECT string FROM string_collection WHERE string LIKE '_n_';

/home/vipin/Pictures/PostgreSQL-LIKE-ILIKE-Pattern-PerformanceIn 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%';

/home/vipin/Pictures/PostgreSQL-LIKE-ILIKE-Pattern-Performance_012.png 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%';

/home/vipin/Pictures/PostgreSQL-LIKE-ILIKE-Pattern-Performance_013.png

 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%';

/home/vipin/Pictures/PostgreSQL-LIKE-ILIKE-Pattern-Performance

A % without any other character matches all strings:

SELECT string FROM string_collection WHERE string LIKE '%';

/home/vipin/Pictures/PostgreSQL-LIKE-ILIKE-Pattern-Performance_010.pngNow 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 '%\%';

/home/vipin/Pictures/PostgreSQL-LIKE-ILIKE-Pattern-PerformanceThis 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 '%\_%';

/home/vipin/Pictures/PostgreSQL-LIKE-ILIKE-Pattern-Performance_014.png

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:

  1. Create a index on the column using the reverse() function.
  2. 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!

The following two tabs change content below.

Vipin Raj

Vipin Raj is a software developer specialized in PostgreSQL Database and Data Modeling, the man behind technobytz and an IoT and Security enthusiast. Having 3+ years of experience in the IT industry, he is currently pursuing his masters in computer science and information security. He spend his free time writing blog posts with the intension of sharing his knowledge to the tech community.

Leave a Reply

Your email address will not be published. Required fields are marked *