PostgreSQL

PostgreSQL Like Clause

Did you know you can use native SQL queries to search for specific matching patterns? You can, and this guide will talk about how to use the LIKE clause in PostgreSQL to get sets matching a specified pattern. Although it may not be as powerful as using RegEx, it can do interesting operations.

Basic Usage

Understanding and using the LIKE clause is simple enough. The clause works by using wildcard characters.

The LIKE clause supports two wildcard characters. These are:

  1. A percent (%) – match a sequence of zero or more characters.
  2. An underscore (_) – match a single character.

The general syntax for using the LIKE statement is as:

expression LIKE pattern

The expression can be a column or a field within a PostgreSQL table.

If the expression matching the specified pattern is found, the LIKE clause returns true and false if otherwise.

NOTE: If you specify a pattern that does not include the supported wildcards, the LIKE clause works similarly to an equality operator.

The following are simple rules for using LIKE wildcards.

‘x’ LIKE ‘xyz’ = false
‘xyz’ LIKE ‘xyz’ = true
‘x’ LIKE ‘%a’ = true
‘x’ LIKE %a%’ = true
‘x’ LIKE ‘_a_’ = true

Like with (%) Wildcard

Let us take an example in the sakila sample database. Suppose we want to find customers whose first name includes an ‘m.’

In such case, we can use the % wildcard search as:

SELECT customer_id, first_name, last_name, email FROM customer WHERE first_name LIKE '%m';

The above query will return all the selected columns for all first_names that include an m.

An example output is below:

To get a clear order of the way the names appear in the database, you can add an order by clause as:

SELECT customer_id, first_name, last_name, email FROM customer WHERE first_name LIKE ‘% m’ ORDER BY customer_id;

In this case, it will show similar results but sorted by customer_id in ascending order.

You can also use double wildcards. Consider the example query below:

SELECT customer_id, first_name, last_name, email FROM customer WHERE first_name LIKE '%mi%' ORDER BY customer_id;

In this example, PostgreSQL will search for a first_name pattern that matches mi. An example result is below:

Like with (_) Wildcard

Next, let us discuss the underscore wildcard. If you specify the _ as the wildcard, PostgreSQL will only check for a single character.

For example, you can check for all names with Emily as:

SELECT customer_id, first_name, last_name, email FROM customer WHERE first_name LIKE ‘Em_ly’ ORDER BY customer_id;

Negating Like

The LIKE clause also comes with a pair. We use the NOT LIKE clause to negate the result of the LIKE clause.

For example, to get only the results that do not include the letter M, we can do:

SELECT customer_id, first_name, last_name, email FROM customer WHERE first_name NOT LIKE '%m%' ORDER BY customer_id LIMIT 5;

This should give a result as one shown below:

You will notice that the above example returns words that start with an M. This is because the LIKE operator is case sensitive, and the location of the wildcard matters.

Closing

The PostgreSQL LIKE query allows you to match specific patterns. You can use it in can queries such as SELECT, INSERT, DELETE, UPDATE to perform operations on matching patterns.

About the author

John Otieno

My name is John and am a fellow geek like you. I am passionate about all things computers from Hardware, Operating systems to Programming. My dream is to share my knowledge with the world and help out fellow geeks. Follow my content by subscribing to LinuxHint mailing list