You want to return rows that matches a particular substring or a pattern.
The LIKE operator in SQL is used to filter rows that matches a particular substring or pattern. For example, let’s say you want to select all the rows for Asia Region but you are not sure how they are represented in your database. So, you want to select all the rows Where Region is Asia but you do not care about what comes before Asia and after Asia. You just want all rows for region column which contains Asia in it.
SELECT Name, Region, Population, LifeExpectancy FROM country WHERE Region LIKE '%Asia%'
The ‘%’ used in the above query is a wildcard character in SQL. It matches any characters in a string. So, ‘%Asia%’ matches any characters that comes before and after Asia.
If you want to match a single unknown character then you have to use underscore ‘_’.
Let’ say I want to find all the countries which starts with ‘Ind’ followed by two unknown characters.
SELECT Name, Region, Population, LifeExpectancy FROM country WHERE Name LIKE 'Ind__'
And If You want you can also combine ‘%’ and ‘_’ wildcard characters together.
SELECT Name, Region, Population, LifeExpectancy FROM country WHERE Name LIKE 'Ind%_'
As we discussed above the % character matches one or more characters that is why in the result along with India, we also have Indonesia.