How to look for partial string matches in queries in PostgreSQL?

PostgreSQLDatabaseData Storage

Suppose you have a table user_info containing the names of users and their addresses. An example is given below −

nameaddress
AnilAndheri, Mumbai, Maharashtra
JoyChandni Chowk, Delhi
RonBandra, Mumbai, Maharashtra
ReenaOld Airport Road, Bengaluru, Karnataka

Now, if you want to just extract the information of users who stay in Mumbai, you can do that using the LIKE command and the % operator.

SELECT * from user_info where address LIKE '%Mumbai%'

The output will be

nameaddress
AnilAndheri, Mumbai, Maharashtra
RonBandra, Mumbai, Maharashtra

Notice that we have added % operator on both sides of Mumbai. This means that anything can precede Mumbai and anything can be after Mumbai. We just want the string to contain the substring Mumbai. If we want the string to start with a specific text, we add the % operator only at the end. For example −

SELECT * from user_info where address LIKE 'Andh%'

The output will be −

nameaddress
AnilAndheri, Mumbai, Maharashtra

On similar lines, if we want the string to end with a specific set of characters, we add the % operator only at the start. For example −

SELECT * from user_info where address LIKE '%Delhi'

The output will be −

nameaddress
JoyChandni Chowk, Delhi


raja
Updated on 02-Feb-2021 13:00:52

Advertisements