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:
- A percent (%) – match a sequence of zero or more characters.
- An underscore (_) – match a single character.
The general syntax for using the LIKE statement is as:
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.
‘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:
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:
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:
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;
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:
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.
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.