The LIKE clause uses two wildcards in its query to specify the condition to be met. We will cover the PostgreSQL LIKE clause using different examples to understand how to fetch the data using various conditions.
Working with PostgreSQL Like Clause
You must understand how the two wildcards work to use the PostgreSQL LIKE clause. The percentage wildcard (%) matches zero, one, or multiple characters or numbers in the specified column. However, the underscore wildcard (_) matches a single character or number.
Let’s give some various examples to implement both wildcards with the PostgreSQL LIKE clause.
Let’s work with the following table throughout the post:
The LIKE clause mainly works with the SELECT statement and has the following syntax:
Example 1: Where the Values Start with a Character/Value
When you want your query to return with different values that begin with a given number or character, you use the percentage wildcard after the target value or character. The following command fetches all entries that end with 400 in the “Salary” column:
When working with integers, add the ::text expression for the LIKE clause to recognize the target number as a character.
However, the command is direct for characters, like in the following example, where we fetch the data with their “lname” which begins with the “te” characters.
Suppose you only want to extract specific data from the table. You can tune the SELECT statement to specify the columns to select, as in the following case. We only select the “fname”, “salary”, and “age” columns and left out the rest.
Example 2: Return Where the Value Ends with a Character/Value
Similar to how we returned the values that start with specified condition, adding a percentage at the start of the specified value returns all the data with the specified character as the last value.
Example 3: Return the Data that Contains Specified Character within the Value
Sometimes, you may have a case where you want to fetch a given value, but you only know that it has a given character or number within. For instance, if the target data contains 21 following each other at any point, you can add %21% as the pattern to retrieve it.
Suppose you want to fetch the data with given characters following each other in a given position. Combine the underscore and the percentage wildcards. The following example retrieves the values in the “fname” column with “oh” as the second and third values:
When you want to specify a condition where you find the values with a given character in the second position and a given character at the end, you can execute your query like the following example. Here, we match the values with the “e” character as the second position and end with “r” as the last character.
Example 4: Find the Values that Start with a Given Character and Have Specific Length
When your target data begins with a given character but you don’t know the other characters, you can specify a pattern to match any data that matches the specified condition. For this example, our search “fname” value begins with “t” and has a length of five characters.
For that, we execute our command as presented in the following:
Example 5: Find the Values of a Given Length Starting and Ending with Specific Characters
When you know the starting and ending character of a given value and the length of the value, you can use the underscore wildcard. The following example fetches the values with their “fname” starting with “t” and ending with “y”, yet the value is five characters long:
Conclusion
The PostgreSQL LIKE clause is ideal when fetching the values that match a given condition. How you use the LIKE clause depends on your query goals and the wildcard that you use. We presented different examples of working with the LIKE clause. With that, you can comfortably use the PostgreSQL LIKE clause.
