Everything You Need to Know About LIKE Operator in SQL

SQL is a language, which consists of multiple commands and operators. But, when you have to retrieve data based on some pattern or characters, then you will need the LIKE operator. So, in this article on LIKE in SQL, I will discuss the following topics:

1. 1. What is LIKE operator?
   2. Syntax of LIKE operator
   3. Different patterns retrieved with LIKE operators
   4. Examples of LIKE operators

What is LIKE in SQL?

This operator is used along with the WHERE clause to retrieve the data according to a specific pattern. There are two wildcards which are used along with the LIKE operator to retrieve data. They are:

• %[Percentage Sign] – It matches 0 or more character.
• _ [Underscore]– It matches exactly one character.

So,  now that I have told you, what is LIKE operator, next, in this article, let us understand the syntax of the LIKE operator.

Syntax of LIKE operator

The syntax of the LIKE operator is as follows:

SELECT column1, coulmn2, . . ., columnN
FROM tablename
WHERE columnName LIKE pattern;

Now, that you have got an idea of the syntax of the LIKE operator, next in this article on LIKE in SQL, let us see the different patterns you can retrieve with the LIKE operator.

Different patterns retrieved with LIKE operator

The different patterns mentioned with LIKE operators are as follows:

Query 1: If you have to find values that start with “x”

Like operation:

WHERE columnname LIKE ‘x%’

Query 2: If you have to find values that end with “x”

Like operation:

WHERE columnname LIKE ‘%x’

Query 3: If you have to find values that have “abc” in any position

Like operation:

WHERE columnname  LIKE ‘%abc%’

Query 4: If you have to find values that have “a” in the third position

Like operation:

WHERE columnname LIKE ‘__a%’

Here, there are 2 underscores present before the letter “a”.

Query 5: If you have to find values that start with “a” and are at least 5 characters in length

Like operation:

WHERE columnname LIKE ‘a____%’

Here, there are 4 underscores present after the letter “a”.

Query 6: If you have to find values that start with “g” and end with “v”

Like operation:

WHERE columnname LIKE ‘g%v’

So, now that I have discussed the various patterns, next in this article on LIKE in SQL, let us look into some examples.

Examples of LIKE operators

Consider the following table on which we will apply various operations of the LIKE operator.

Q1. Select all students starting with “a”

SELECT * FROM students
WHERE studentname LIKE 'a%';

Output:

Q2. Select all students with a studentname ending with “i”

SELECT * FROM students
WHERE studentname LIKE '%i';

Output:

Q3. Select all students with a studentname that have “li” in any position

SELECT * FROM students
WHERE studentname LIKE '%li%';

Output:

Q4. Select all students with a studentname that have “o” in the second position:

SELECT * FROM students
WHERE studentname LIKE '_o%';

Output:

Q5.  Select all students with a studentname that start with “a” and are at least 5 characters in length

SELECT * FROM students
WHERE studentname LIKE 'a____%';

Output:

Q6.  Select all students with a studentname that start with “s” and end with “y”

SELECT * FROM students
WHERE studentname LIKE 's%y';

Output:

With this, we come to an end to this article. I hope you understood how to use the LIKE clause to retrieve various kinds of data. If you wish to learn more about MySQL and get to know this open-source relational database, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject.

Got a question for us? Please mention it in the comments section of this article and I will get back to you.