Introduction to PostgreSQL Like
Whenever you are dealing with string manipulation in PostgreSQL, it often needs to match the strings with a particular pattern and then perform some action and operation on the string if match successes are performed. This is one of the most common use-cases when it comes to string comparisons and manipulation. The traditional way to perform pattern matching that is used in SQL is by using the LIKE expression. Many other methods were introduced in PostgreSQL for pattern matching, such as POSIX regular expression matching and SIMILAR TO expressions that can also be used for pattern matching. This article will learn about the syntax of LIKE expression and how we can use it in PostgreSQL to perform pattern matching in strings.
Syntax of PostgreSQL Like
demoString LIKE anyPattern [ESCAPE characterThatNeedsToBeEscaped]
demoString NOT LIKE anyPattern [ESCAPE characterThatNeedsToBeEscaped]
DemoString: It can be any string value or the column of the particular table that stores string and that you want to check whether matches a pattern or contains some characters or substring.
AnyPattern: The pattern is a particular string that you want to match with the demoString. It may contain the %(percentage) sign and _(underscore) sign that help in defining the pattern. The underscore sign mentions that any character might be present on that particular position of the pattern string, and % specifies the presence of one or more characters before or after or in-between the pattern string wherever it is being mentioned in the pattern string. We will discuss it in detail in the upcoming session and have examples to clarify the concept.
CharacterThatNeedsToBeEscaped: Whenever you go for pattern matching, there are certain characters in the demoString that is the original string that you wish to skip while matching. These characters are called escape characters. By default, the backslash is considered to be an escape character that will be skipped while pattern matching. If you want to consider backslash in pattern matching, just specify double backslash as the escape character. You can specify any other character you want to skip in CharacterThatNeedsToBeEscaped parameter. This field is optional and has backslash as the default value.
Functions of PostgreSQL Like
When you use LIKE expression to match a certain string with the pattern, if the pattern matches, then true is returned; else, false is returned by the like expression. NOT LIKE expression behaves oppositely. It returns true when the string does match the pattern and false when the match is successful. demoString NOT LIKE anyPattern [ESCAPE characterThatNeedsToBeEscaped] is equivalent to NOT (LIKE anyPattern [ESCAPE characterThatNeedsToBeEscaped] ).
The pattern can be any string that you want to compare and contain underscore and percent signs to match multiple cases. When no such signs are mentioned in the pattern and are a plain string, then the Like behaves the same as that of an equal operator. When the underscore is there, then that position can be occupied by any other character in the original string to be matched. While the percent sign makes sure that there can be the presence of zero or more characters wherever it is mentioned. Let us study all these cases one by one.
Examples to Implement of PostgreSQL Like
Let us create one example and insert a few records in the table to learn how to use a LIKE expression for pattern matching. Open your PostgreSQL command-line prompt and enter the following command to create a table named educba:
Example #1
Query:
CREATE TABLE educba
(id INTEGER PRIMARY KEY,
technologies VARCHAR,
workforce INTEGER,
address VARCHAR);
Output:
Example #2
Let us insert some values in the educba table using the following statement:
Query:
INSERT INTO educba VALUES (1,'java',20,'satara'),(2,'javascript',30,'mumbai'),(3,'java',20,'satara'),(4,'psql',30,'mumbai'),(5,'mysql',20,'satara'),(6,'maven',30,'mumbai'),(7,'hibernate',20,'satara'),(8,'spring',30,'mumbai'),(9,'angular',20,'satara'),(10,'html',30,'mumbai'),(11,'css',20,'satara'),(12,'reddis',30,'mumbai');
Output:
Example #3
Let us create a simple select query statement to retrieve all the records from the educba table. Our query statement will be as follows.
Query:
SELECT * FROM educba;
Here, * represents all the columns to be retrieved and firing above query results in the following output:
Output:
Example #4 – String Matching a String
Let us take a pattern without any underscore or percent sign in it and match it with a LIKE expression.
Query:
SELECT technologies FROM educba WHERE technologies LIKE 'jav';
Output:
As there is no record with a technologies column having jav value, it returns zero rows.
Example #5
Let us now fire the following query.
Query:
SELECT * FROM educba WHERE technologies LIKE 'java';
Output:
There are two records with java named technologies; hence two records are retrieved.
Example #6 – String Matching with _ in a Pattern
Now, we will retrieve all the records that will have underscore for the first position and then the string SQL for technologies column value. For this, we will fire the following query statement –
Query:
SELECT * FROM educba WHERE technologies LIKE '_sql';
that gives the following output: there is just one record with technologies value psql that matches the pattern.
Output:
Example #7
Now, we will retrieve the records that have __ two underscores in the first and second position and then the string SQL using the following query statement.
Query:
SELECT * FROM educba WHERE technologies LIKE '__sql';
Output:
Only one record with MySQL technologies value is there that matches the pattern specified by us.
Example #8
Similarly, we can place underscore any number of times at any position we wish to build up our pattern. When we want to find records having technologies field value such that it contains one character previous and one character after the av substring, we will fire the query statement as follows –
Query:
SELECT * FROM educba WHERE technologies LIKE '_av_';
Output:
Example #9
Inserting one more _ in the above pattern gives the following query:
Query:
SELECT * FROM educba WHERE technologies LIKE '_av__';
Output:
Example #10 – String Matching with % in a Pattern
Suppose we want to retrieve all the records having string SQL in the ending and having any number of characters before it in the technologies field. Then we can make the use of the % sign that checks for zero or more character presence, and my query statement will be as follows:
Query:
SELECT * FROM educba WHERE technologies LIKE '%sql';
that results in the output containing psql as well as MySQL as there can be any number of characters before SQL string in the technologies field value.
Example #11
Now, for retrieving records containing av in between the string and present anywhere in a technologies field value, we will have to mention % before and after av to search for substring av presence in an original string of technologies field value. Y query statement will be as follows.
Query:
SELECT * FROM educba WHERE technologies LIKE '%av%';
Output:
Conclusion
We can perform pattern matching using the LIKE expression in PostgreSQL to retrieve the records that match the particular pattern. Further, we can perform operations based on whether they match the pattern, such as splitting or replacing the original string’s substring.
Recommended Articles
This is a guide to PostgreSQL Like. Here we discuss the Introduction to PostgreSQL Like and its working along with practical examples and different subquery expressions. You can also go through our suggested articles to learn more –
