PostgreSQL provides a LIKE operator that performs pattern/text matching using wildcards. The LIKE operator returns true(Boolean value) if a perfect match is found in the targeted text.

PostgreSQL provides two wildcards, percent (%) and underscore(_), that are used with the aid of the LIKE operator. If you didn’t specify a wildcard in the LIKE operator, then it will act as an equal operator.

This write-up will discuss both wildcards(% and _) with the help of examples. So, let’s start!

How to Use the LIKE Operator/Clause in PostgreSQL?

The wildcard percent “%” in Postgres is used to specify zero, one, or more than one character/number. While the wildcard underscore “_” is used to specify a single character/number. To avail maximum functionalities, both these operators/clauses can be used combinedly.

There are several syntaxes of the Postgres LIKE operator; you can use any of the below-mentioned syntaxes depending on the situation:

Syntax 1: How to Find a Value That Starts With “XYZ” Using Like Operator/Clause?

The below syntax will be used to search for a value that starts with “XYZ”.

SELECT FROM   tab_name
WHERE col_name LIKE 'XYZ%';

Following is a step-by-step explanation of the above syntax:

- The above syntax illustrates that the string must start with XYZ.

- The percent “%” wildcard at the end represents that there can be zero or more characters after the specified value i.e. XYZ.

Note: Here, XYZ is just an exemplary value. It can be any string or a number.

Syntax 2: How to Use Like Operator/Clause to Find a Substring?

The following syntax of the LIKE operator is used to search a substring at any position; let’s say XYZ is the targeted substring:

SELECT FROM   tab_name
WHERE col_name LIKE '%XYZ%';

In the above syntax, the percent % wildcard at the start and end represents that there can be zero, one, or more characters/numbers at the beginning or at the end of the string. However, the string must contain a substring “XYZ''.

Syntax 3: How to Use Like Operator to Find a Value That Ends With a Specific Character/Number?

Follow the below-given syntax for the LIKE operator to find a value that ends with a specific value, character, or number:

SELECT FROM   tab_name
WHERE col_name LIKE '%5';

Here, “%5” shows that there can be anything at the start of the string. However, the string must end with 5.

Syntax 4: How to Find a Value at a Specific Position Using Like Operator/Clause?

The underscore “_” wildcard is used to find a value at a specific position:

SELECT FROM   tab_name
WHERE col_name LIKE '_Z';

The underscore “_” at the start shows that at the beginning, there must be only a single character and the second character must be “Z”.

Syntax 5: How to Find a Value at a Specific Position and That Ends With a Specific Character?

Now, we have to consider two conditions simultaneously, i.e., X should be the second character in the string, and the string must end with Y. To do so, we will use both wildcards combinedly with the aid of the LIKE operator as shown in the below syntax:

SELECT FROM   tab_name
WHERE col_name LIKE '_X%Y';

Let’s illustrate the above syntax stepwise:

- The underscore at the beginning represents that there can be any character at the start of a string.

- The second position must hold an “X”.

- The % sign shows that there can be zero, one, or more characters in between X and Y.

- The string must end with the character “Y”.

Syntax 6: How to Find a String That Has At Least Two Characters Using Like Operator/Clause?

Follow the below-given syntax for the LIKE operator to get a substring of at least two characters:

SELECT FROM   tab_name
WHERE col_name LIKE '%_%_%';

As we have discussed earlier, the underscore “_” is used to specify a single character, while the “%” is used to specify zero or more characters. In this syntax, we utilized two underscore wildcards, “_” and three percent wildcards, “%”. So, the LIKE operator will fetch all the strings of length two or more.

Similarly, you can use these wildcards with the collaboration of the LIKE operator to perform different functionalities.

Example #1: How to Select All the Teams Whose Names Start With “S” Using Like Operator?

We already have a table named team_details that contains the following data:

SELECT * FROM team_details;

Suppose we have to fetch only those teams whose names start with ‘S’; to do so, we will execute the following query:

SELECT * 
FROM team_details 
WHERE team_name LIKE 'S%';

In this example, we utilized % wildcard along with the LIKE operator to fetch those teams whose names start with S. The output authenticates the working of percent wildcard and LIKE clause.

Example #2: How to Select All the Teams Whose Names Contain a Small “s”?

To fetch the team names that contain a letter s, we will enclose the targeted letter within the percent wildcards:

SELECT * 
FROM team_details 
WHERE team_name LIKE '%s%';

The output authenticates the working of the LIKE clause in PostgreSQL.

Example #3: Select the Teams Whose Second Letter is “a”

Suppose we have to fetch those teams whose names start with any letter, but the second letter must be “a”. To do so, we will combine both the wildcards as follows:

SELECT * 
FROM team_details 
WHERE team_name LIKE '_a%';

The underscore _ wildcard represents that the first letter can be anything. Next comes “a” which represents the second letter must be a. While the percent wildcard at the end shows that there can be single or multiple letters followed by the letter a.

The output clarified that the LIKE clause succeeded in fetching those teams whose names start with any letter, but their second letter is “a”.

In this way, you can use any wildcard with the collaboration of the LIKE operator/clause to achieve various functionalities.

Conclusion

To perform pattern matching in PostgreSQL, the LIKE operator uses two wildcards (percent % and underscore _). The wildcard percent “%” in Postgres is used to specify zero, one, or more than one character/number. While the wildcard underscore “_” is used to specify a single character/number. To avail maximum functionalities, both these operators/clauses can be used combinedly. This post considered several examples to explain the working of the LIKE operator/clause in a better way.