Introduction to PostgreSQL Compare Strings
PostgreSQL Compare Strings compare the two string and return the result as we have specified the input, we can also update the rows using comparing strings in PostgreSQL. We can compare the string using like clause in PostgreSQL, we can also compare the string using the =, !=, <>, <, >, <= and >= character string operator. Basically character string operator in PostgreSQL is used to compare the string and return the result as we specified input within the query. Comparison operator in PostgreSQL compares the two string values with each other and retrieves or update the rows.
How to Compare Strings in PostgreSQL?
We have used like and comparison operator for comparing two strings in PostgreSQL. Comparison operator is very useful while comparing two strings. We are using stud_str table to describe example of compare string in PostgreSQL as follows.
Below is the table and data description of stud_str table.
Code:
select * from stud_str;
\d+ stud_str;
Output:
Below is the comparison operator which we have used in PostgreSQL while comparing string in PostgreSQL.
- < (Less than): It will return the true value when left string is less than right string.
- > (Greater than): It will return the true value when left string is greater than right string.
- <= (Less than or equal to): It will return the true value when left string is less than equal to right string.
- >= (Greater than): It will return the true value when left string is greater than equal to right string.
- <> or != (Not equal): It will return the true value when the given string is not equal to each other.
- = (Equal): It will return the true value when the two string are same also the type of the string is same.
Examples of PostgreSQL Compare Strings
Given below are the examples mentioned:
Example #1
Compare string using equality operator.
- We can compare the string using equality operator. Below example shows that we are comparing the string using equality operator and retrieving the result using select operations.
- In below example we have compared the string ABC with PQR to retrieve result from stud_str table.
Code:
select * from stud_str where first_name = 'ABC' and last_name = 'PQR';
Output:
- Below example shows that we are comparing the string and updating the rows using update operations.
- In below example we have compared the string ABC with PQR to update the rows from stud_str table.
Code:
update stud_str set id = 11 where first_name = 'ABC' and last_name = 'PQR';
select * from stud_str;
Output:
Example #2
Compare string using greater than operator.
- We can compare the string using greater than operator. Below example shows that we are comparing the string using greater than operator and retrieving the result using select operations.
Code:
select * from stud_str where first_name > 'ABC' and last_name > 'PQR';
Output:
- Below example shows that we are comparing the string using greater than operator and updating the rows using update operations.
Code:
update stud_str set id = 12 where first_name > 'ABC' and last_name > 'PQR';
select * from stud_str;
Output:
Example #3
Compare string using like operator.
- We can compare the string using like operator. Below example shows that we have comparing the string using like operator and retrieving the result using select operations.
Code:
select * from stud_str where first_name like '%BC' and last_name like 'PQ%';
Output:
- Below example shows that we are comparing the string using like operator and updating the rows using update operations.
Code:
update stud_str set id = 101 where first_name like '%BC' and last_name like 'PQ%';
select * from stud_str;
Output:
Example #4
Compare string using less than operator.
- We can compare the string using less than operator. Below example shows that we are comparing the string using less than operator and retrieving the result using select operations.
Code:
select * from stud_str where first_name < 'ABC' and last_name < 'PQR';
Output:
Example #5
Compare string using less than equal to operator.
- We can compare the string using less than equal to operator. Below example shows that we are comparing the string using less than equal to operator and retrieving the result using select operations.
Code:
select * from stud_str where first_name <= 'ABC' and last_name <= 'PQR';
Output:
- Below example shows that we are comparing the string using less than equal to operator and updating the rows using update operations.
Code:
update stud_str set id = 105 where first_name <= 'ABC' and last_name <= 'PQR';
select * from stud_str;
Output:
Example #6
Compare string using greater than equal to operator.
- We can compare the string using greater than equal to operator. Below example shows that we are comparing the string using greater than equal to operator and retrieving the result using select operations.
Code:
select * from stud_str where first_name >= 'ABC' and last_name >= 'PQR';
Output:
Conclusion
We have used =, !=, <>, <, >, <= and >= comparison operator to compare the string in PostgreSQL. Also we have used like operator to compare the string. We have compared the string using the select and update operations in PostgreSQL.
Recommended Articles
This is a guide to PostgreSQL Compare Strings. Here we discuss the introduction, how to compare strings in PostgreSQL? and examples respectively. You may also have a look at the following articles to learn more –