AWS Global Infrastructure

Databases

Topics Covered
  • Cassandra (14 Blogs)
  • MongoDB Dev and Admin (15 Blogs)
  • MySQL (30 Blogs)
  • SQL Essentials Training and Certification (4 Blogs)
SEE MORE

Everything You Need to Know About LIKE Operator in SQL

Last updated on Mar 09,2023 5.4K Views

32 / 37 Blog from Introduction to 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:

SQL - Like in SQL - Edureka

    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.

For a detailed, You can even check out the details of relational databases, functions, and queries, variables, etc with the SQL Training.

Examples of LIKE operators

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

studentIDstudentname
1akash
2mitali
3sanjay
4anuj
5sonali

Q1. Select all students starting with “a”

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

Output:

studentIDstudentname
1akash
4anuj

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

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

Output:

studentIDstudentname
2mitali
5sonali

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

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

Output:

studentIDstudentname
2mitali
5sonali

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

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

Output:

studentIDstudentname
5sonali

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:

studentIDstudentname
1akash

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

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

Output:

studentIDstudentname
3sanjay

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.

Comments
0 Comments

Join the discussion

Browse Categories

webinar REGISTER FOR FREE WEBINAR
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP

Subscribe to our Newsletter, and get personalized recommendations.

image not found!
image not found!

Everything You Need to Know About LIKE Operator in SQL

edureka.co