PostgreSQL Like

In this section, we are going to understand the working of PostgreSQL Like condition which is used to fetch data using pattern matchings, examples of the like condition by using percent (%), underscore (_) wildcards, Not Like Operator, working of ILIKE operator, and the extension of PostgreSQL Like Operator.

Introduction of PostgreSQL Like condition

The PostgreSQL Like condition is used to fetch data from a table where the defined condition satisfies the LIKE condition. The result contains strings, which are case-sensitive and follow the specified pattern.

In other words, we can also say that the Like condition is used to perform pattern matching for identifying the exact outcome.

To serve the purpose of patterns matching, the PostgreSQL provides two different wildcard characters, which are as follows:

• Percent (%) wildcard: To match any sequence of zero or more characters.
• Underscore (_) wildcard: To match any single character.

PostgreSQL Like Condition Syntax

In PostgreSQL, the Like condition can be used with the SELECT, INSERT, UPDATE, and DELETE commands and the WHERE clause.

expression LIKE pattern [ ESCAPE 'escape_character' ]  

In the above syntax, the Like condition is used, if the value matches the pattern, then the expression will return true.

Or

We can use the NOT operator syntax to oppose the LIKE operator as follows:

expression NOT LIKE pattern[ ESCAPE 'escape_character' ]  

In the above syntax, the Not Like condition is used when the value does not match, then the pattern and returns true.

In the above syntaxes, we have used the following parameters:

Note:

• If we do not want to specify the escape_character, PostgreSQL assumes that "\" is the escape_character.
• The Like operator behaves like the equal "=" Operator if the pattern does not have any wildcard character.

Example of PostgreSQL Like Condition using Percent (%) wildcard

Let us see an example to understand how the PostgreSQL Like condition works:

Suppose we want to identify an employee, but we do not accurately remember his/her name. But we know his/her name starts with something like kat.

Now the question arises, how do we identify the particular employee from the database?

So, we may identify the employee in the Employee table by seeing at the emp_fname column to check if there is any value that starts with kat.

If the Employee table has many rows, and we follow the above process, it might take more time.

Therefore, in the below example, we are using the PostgreSQL Like condition to match the employee's initial name with a string as we can see in the following command:

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname LIKE 'Kat%';

Output

After executing the above command, we will get the following result:

The above statement returns row whose values in the emp_fname column starts with Kat and may be followed by any sequence of characters, known as pattern matching technique.

Note1:

• We can define the pattern by joining the literal values with wildcard characters.
• To identify the matches, we can use the LIKE or NOT LIKE operator.
• The WHERE clause can have a distinct expression: the emp_fname, where the LIKE Operator and a string has a percent sign (%).
• And the 'Kat%' string is known as a pattern.

Example of PostgreSQL Like operator: pattern matching

Let us see some examples of pattern matching by using the LIKE operator:

Note: In PostgreSQL, we can use the wildcards at the starting and ending of the pattern.

In the below example, the particular command will return those employees whose emp_fname contains "in" string such as Katherine, Katrina, Karina, etc.

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname LIKE '%in%'
ORDER BY emp_fname;

Output

After implementing the above command, we will get the following output:

Example of Underscore (_) Wildcard using Like Operator

Here, we are using the above Employee table as well to display the working of underscore (_) wildcard with the help of Like Operator.

In the below example, we will emp_fname, emp_lname, and location from the employee table, and using the underscore wildcard within emp_lname begin with any character, followed by "mith":

SELECT emp_fname, emp_lname, location
FROM employee
WHERE emp_lname LIKE '_mith';

Output

After executing the above command, we will get the following result:

Now, if we want to join the Underscore (_) and Percent (%) wildcard together to create a pattern as we can see in the following command:

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname LIKE '_at%'
ORDER BY emp_fname;

Output

On executing the above command, we will get the following outcome:

The above pattern _at% matches any string that should start with any single character (_), the literal string should follow the string at, the string should be ended with any number of characters. It will return emp_fname as Katerine and Katrina.

Note:

• To match a character or a sequence anywhere within a string, the pattern must start and end with a percentage (%) sign as the Like pattern matching always contains the entire string.
• The Search conditions can have either numbers or the literal characters: where the percent (%) sign matches any sequence of zero or more characters.
• The underscore sign (_) in the pattern is used to match any single character.

Example of PostgreSQL Not Like Condition

We can also use NOT Operator with PostgreSQL LIKE condition. The following example displays Not Like Operator's use to identify the employee whose emp_fname does not start with Kat:

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname NOT LIKE 'Kat%';

Output

After implementing the above statement, we will get the following result:

Extensions of PostgreSQL LIKE operator

In PostgreSQL, we also have the ILIKE operator, which works similarly to the LIKE operator.

In other words, we can say that the ILIKE operator equates the value case-insensitively.

In the following example, we will use the ILIKE operator into the employee table:

SELECT emp_fname, emp_lname
FROM employee
WHERE emp_fname ILIKE 'ka%';

Output

On executing the above command, we will see the following result:

The ka% pattern matches any string that starts with Ka, kA, ka, KA, and so on. If we use the LIKE operator in place of ILIKE operator, the command will return an empty result.

The PostgreSQL also supports some other operators, which perform like the LIKE, ILIKE, NOT LIKE and NOT ILIKE operator as we can see in the below table:

Overview

In the PostgreSQL Like Condition section, we have learned the following topics:

• We used the PostgreSQL Like condition with Percent (%) and Underscore (_) wildcards.
• We used the Not Like operator to get the records from the particular table.
• We used the Percent (%) and Underscore (_) wildcards together for match patterns.
• We learned the ILIKE operators, which is used to fetch the data using pattern matching.
• We understood the extension of PostgreSQL Like Operator.