PostgreSQL Compare Strings

Updated May 18, 2023

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 a like clause in PostgreSQL, we can also compare the string using the =, !=, <>, <, >, <= and >= character string operator. Basically, the 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 used the like and comparison operator to compare two strings in PostgreSQL. Comparison operator is very useful while comparing two strings. We are using the stud_str table to describe an example of compare string in PostgreSQL as follows.

Below is the table and data description of the 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 the left string is less than the right string.
• > (Greater than): It will return the true value when the left string is greater than the right string.
• <= (Less than or equal to): It will return the true value when the left string is less than equal to the right string.
• >= (Greater than): It will return the true value when the left string is greater than or equal to the 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 the same, and the string type is the same.

Examples of PostgreSQL Compare Strings

Given below are the examples mentioned:

Example #1

Compare string using the equality operator.

• We can compare the string using the equality operator. Below example shows that we are comparing the string using the equality operator and retrieving the result using select operations.
• In the below example, we have compared the string ABC with PQR to retrieve the result from the 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 the below example, we have compared the string ABC with PQR to update the rows from the 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 the greater than operator. Below example shows that we are comparing the string using the 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 the 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 the like operator.

• We can compare the string using the like operator. Below example shows that we have comparing the string using the 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 the 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 the operator.

• We can compare the string using less than the 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 the operator. Below example shows that we are comparing the string using less than equal to the 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 the 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 the 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 a like operator to compare the string. We have compared the string using the select and update operations in PostgreSQL.

We hope that this EDUCBA information on “PostgreSQL Compare Strings” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

1. PostgreSQL Recursive Query
2. hstore in PostgreSQL
3. PostgreSQL DECODE()
4. PostgreSQL Inner Join