SQL Tutorial – LIKE operator for Pattern Matching.

You want to return rows that matches a particular substring or a pattern.

Solution –

LIKE –

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.