Home » MySQL

MySQL LIKE Operator

MySQL | LIKE Operator: Learn about the MySQL LIKE Operator, with its explanation, syntax, and query examples.
Submitted by Apurva Mathur, on September 13, 2022

LIKE Operator

Whenever we are asked to find any sort of pattern in the database we use the LIKE operator. LIKE operators are used to find out the patterns using some query.

Need of LIKE Operator

Imagine a case where you want all the names starting with "a", what will you do in such a case? Will you arrange them alphabetically, then you will take the names starting from "a"? It is a long and outdated procedure, one quick solution is to use the LIKE operator, this operator is used in such a way that you will directly get the result. LIKE operator is helpful in cases like where you want to find out the names ending with any specific alphabet, or when you want to find out names having some specific characters in between.

LIKE Operator Syntax

SELECT * FROM table_name
WHERE column_name LIKE 'pattern';

Creating Pattern for LIKE Operator

While defining a pattern we use the following symbols:

• % (Percentage symbol) this generally means 0, one, or multiple characters.
  For example, if want to find out any name starting with z then in such case will be written; Z%
• _ (underscore) this symbol means any single character.
  For example, if I write "R_" this means R should be the first character and the next character can be anything.

Let us understand this by taking some cases;

LIKE Operator Examples

Suppose we have a table named "student_details" and inside this table, we have the following columns and rows;

Case 1: Finding names starting with the letter "S"

In such case our query will be:

SELECT * FROM student_details 
WHERE Student_name LIKE 'S%';

As you can see we have a total of 7 names starting with "S".

Case 2: Finding the names ending with "a"

In such case, we will write;

SELECT * FROM student_details 
WHERE Student_name LIKE '%a';

Case 3: Finding some specific character in between

Suppose we want to find the name whose one and the last character can be anything but the second character should be "a", in such case our query will be;

SELECT * FROM student_details 
WHERE Student_name LIKE '_a%';

As you see, the result has "a" in the second position.

Case 4: Finding some specific character in starting and end

Suppose we want to find out the name starting with "s" and ending with "a" then in such case we will write,

SELECT * FROM student_details 
WHERE Student_name LIKE 'S%a';

Case 5: Finding a name that only has 4 characters

If we are asked to find out the names which only have 5 characters in it, in this case, our query will be;

For this case, we will put 5 underscores.

SELECT * FROM student_details
WHERE Student_name LIKE '_____';

All the names which have only 5 characters will be displayed on your screen.

Points to remeber while writing LIKE query

• When you are writing any pattern make sure you check the case of the letter,
  For example, if you have the first letter capital in the data and while writing the query you are writing it in the small letter then it won't show any result.
• While writing the query don't forget to write your pattern in '' (inverted commas) else it will throw you the syntax error.
• If you don't have any data according to the query written, then this will return you the empty set.
• You can use AND / OR clause with WHERE conditions.

---