Understanding the SQL LIKE Operator

The SQL LIKE operator is used in the WHERE clause to search for specific patterns in a column’s data. It is highly useful for flexible and dynamic filtering of text data in databases.

• %: Matches zero, one, or multiple characters.
• _: Matches exactly one character.

Example 1: Starts With

To find all people whose names start with the letter “A” in our Indian people table, use the % wildcard at the end of the pattern.

SELECT * FROM IndianPeople
WHERE Name LIKE 'A%';
        

Output: The result will include all people whose names start with “A”:

Example 2: Contains

To find all people whose city contains the letter “M”, use the % wildcard at both ends of the pattern.

SELECT * FROM IndianPeople
WHERE City LIKE '%M%';
        

Output: The result will include people who live in cities that contain the letter “M”:

Example 3: Wildcard with Single Character

You can use the underscore (_) wildcard to match a single character. For example, if we want to find all people whose names are 5 characters long and start with “M”:

SELECT * FROM IndianPeople
WHERE Name LIKE 'M____';
        

Output: The result will include all names that start with “M” and have exactly 5 characters.