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

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

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

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 −

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 −

Yash Sanghvi

Updated on: 02-Feb-2021

4K+ Views

Kickstart Your Career

Get certified by completing the course

Get Started