Postgres LIKE vs EQUAL
Introduction
The LIKE and EQUAL conditions are used to compare data from the database using matching techniques. LIKE condition uses pattern matching while the EQUAL condition compares the input with the original data to ensure they are equal. Thus, to obtain a true case using EQUAL, both the input data and the original data must be same, otherwise it is not true. On the other hand, the LIKE condition is true if part of the original data matches the input data. It is false otherwise if no part of the original data matches the input data.
To bring out the difference between the two we will consider a scenario where a school database administrator requires searching for an alumni student details from the school database. In some cases, the students will remember their complete registration number and in other cases, the students will remember part of their registration number. To enable the administrator to get the students who remembers only part of their registration number, one can use the LIKE condition to filter the list of the students who have similar records. Thus in most case, LIKE is used to obtain matching patterns while EQUAL is used to obtain equivalent patterns. LIKE is mostly used in Google search engine, or searching matching words in most editing tools like Microsoft office.
Stick with us as we examine PostgreSQL LIKE vs EQUAL and look at some practical examples together with the WHERE clause.
LIKE condition
The LIKE condition is often used when filtering rows with string data. The syntax to filter data is as follows:
A query returns true if the string matches the given pattern. Otherwise, it returns false. Additionally, you can still combine the like with negation operator NOT to obtain NOT LIKE patterns. NOT LIKE list the strings which do not match the given pattern.
Application example
We shall use the student table to illustrate the application of LIKE in PostgreSQL. We first select the data to see what is in the table
id | name | age | gender | marks
-- -- + -- -- -- -- - + -- -- - + -- -- -- -- + -- -- -- -
1 | tito | 15 | MALE | 68
2 | PAUL | 18 | MALE | 8
3 | SAILOR | 14 | MALE | 90
4 | QUEEN | 15 | FEMALE | 18
5 | tito | 15 | MALE | 68
6 | ticoon | 15 | MALE | 68
7 | kito | 15 | MALE | 68(7 rows)
Now we select the list of student whose name matches the pattern “ti” using the following query;
Results
id | name | age | gender | marks
----+---------+-----+--------+-------
1 | tito | 15 | MALE | 68
5 | tito | 15 | MALE | 68
6 | ticoon | 15 | MALE | 68
(3 ROWS)
In other cases, you can use an underscore and the percent symbol to search for specific patterns preceded by any other character. Example
Results
EQUAL condition
The equal condition filters the string which is completely identical to the given patern. We use the following statement to obtain equal patterns;
Example
The result does not indicate any result when there is no string matching the given pattern as shown below.
Therefore, EQUAL is used when we need to obtain specific rows with details equal to input string.
Conclusion
Thank you for joining us. We hope you now have a greater understanding of LIKE vs EQUAL in SQL.
Pilot the ObjectRocket Platform Free!
Try Fully-Managed CockroachDB, Elasticsearch, MongoDB, PostgreSQL (Beta) or Redis.
Get Started