In PostgreSQL, wildcards are used to perform pattern matching. PostgreSQL supports two types of wildcards represented with a percentage sign “%,” and an underscore sign “_”. The percentage wildcard "%" matches sequences of characters, while the underscore wildcard "_" matches a single character. These wildcards are used with the LIKE operator to perform the pattern matching.

This write-up will teach you what wildcards are and how to use them in Postgres. So, let’s begin!

Underscore Wildcard “_” in Postgres

In PostgreSQL, the underscore wildcard is used to signify only a single number/character. For instance, the below-given syntaxes will be used to achieve different functionalities in Postgres:

_x

The above pattern will be used to fetch all those values that start with anything but their second letter must be “x”.

_x_

The above pattern indicates that you can place anything at the first and third index however the second index must have the letter “x”.

This way, you can create different patterns using the underscore wildcard.

Percentage Wildcard “%” in Postgres

In PostgreSQL, the percentage wildcard is used to signify zero, one, or more than one number/character. For instance, the below-given syntaxes will be used to achieve different functionalities in Postgres:

%xx%

The above pattern will fetch all those values containing a substring “xx”.

%xx

The above pattern indicates that it will be used to find all those strings that end with “xx”.

xx%

The above pattern indicates that it will be used to find all those strings that start with “xx”.

This way, you can use the percentage wildcard to match the pattern.

Note: Use both wildcards combinedly to achieve maximum functionality.

Example 1: Find a Substring “Function”

We have created a table named “article_info”, whose data is shown in the following snippet:

SELECT * FROM article_info;

We will utilize the percent wildcard to find a substring “function” from the article_title column of the given table:

SELECT article_title 
FROM article_info
WHERE article_title LIKE '%Function%';

The output snippet proves that there are two titles in the “artile_info” table that contain a substring “Function”.

Example 2: Find a Title Whose Second Last Letter is “r”

In this example, we will use the underscore wildcard to find all those titles whose second last letter is “r”

SELECT article_title FROM article_info
WHERE article_title LIKE '%r_';

In the above example, we utilized the combination of both percent and underscore wildcards. The % sign states there can be anything at the start of the string. The second last letter must be “r”, and the underscore wildcard at the end of the string indicates that there will be only one letter after “r”:

The output snippet authenticates the working of the Postgres wildcards.

Conclusion

Pattern matching in PostgreSQL is performed using wildcards. PostgreSQL offers two wildcards represented with a percentage sign “%” and an underscore sign “_”. The percentage wildcard "%" matches sequences of characters/numbers, while the underscore wildcard "_" matches a single character/number. This write-up explained how to use wildcards in Postgres using practical examples.