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 13:00:52

• Related Questions & Answers
• How to Kill queries in pgAdmin in PostgreSQL?
• How to replace total string if partial string matches with another string in R data frame column?
• How to apply DISTINCT constraint on select columns in queries in PostgreSQL?
• Queries for characters in a repeated string in C++
• With JavaScript RegExp how to search a string for text that matches regexp?
• How to remove partial string after a special character in R?
• How to write JavaScript Regular Expression for multiple matches?
• How to create a table in PostgreSQL?
• MongoDB query for Partial Object in an array
• Aliasing in PostgreSQL?
• How to delete partial data in MongoDB?
• How to look beautiful in 15 days?
• Search for partial value match in an Array in PHP
• How to Query a DB in pgAdmin in PostgreSQL?
• What to look for before buying a used car?