Search by pattern using LIKE SQL

In the previous article in this series, we learned the keyword WHERE (where), which allows us to select rows from the table that correspond to a condition or several conditions at once (AND union) or at least one of them (OR union).

In the conditions, we checked the equality or inequality of data and some value. If you have already forgotten, go back and re-read.

We will devote this mini lesson to another search option applicable to string variables – pattern search. Let’s say we need to find all students named Ilya. In this case, we cannot use strict equality and if we execute the following query, we will not receive a single row.

SELECT name, course, score FROM students WHERE name = ‘Илья’;

If you don’t believe me, try it yourself Here.

The fact is that the name column of our table contains both the first and last names of students, and the following query should be formulated correctly: SELECT name, course, grade FROM students WHERE the name begins with the word Ilya. To translate such a query into English SQL, you need to remember one more word – LIKE (similar) and write the condition WHERE name LIKE ‘Ilya%’; That is, where the name is similar to some pattern (in our case ‘Ilya%’).

What does the % sign mean in the pattern? This is a designation of the fact that in the column after the word Ilya, any symbols in any (even zero) quantity can follow.

So let’s run the query:

SELECT name, course, score FROM students WHERE name LIKE ‘Илья%’;

And we get the following result:

|———–|———–|——-| | name | course | score | |———–|———–|——-| | Ilya Krasnov | Literature | 3 | | Ilya Dolgov | Physics | 3 | | Ilyas Mukhameddinov | History | 4 |

As you can see, the result includes records about two students named after Ilya and one with the name Ilyas, which fully meets the requirements of the request.

Another case is if we need lines ending with a certain set of letters. For example, if we use the ‘%iya’ pattern to search for the course field and execute the query:

SELECT name, course, score FROM students WHERE course LIKE ‘%ия’;

As a result, we get a list of historians and geographers. If it is not clear why this is so, write in the comments.

|———–|———–|——-| | name | course | score | |———–|———–|——-| | Ilyas Mukhameddinov | History | 4 | | Vera Krpikova | Geography | 4 | | Tatiana Govorova | History | 5 |

If we use the ‘%ov%’ template for the name field, we will get a list of all students whose first or last name contains this letter combination anywhere.

SELECT name, course, score FROM students WHERE name LIKE ‘%sov%’; |———–|———–|——-| | name | course | score | |———–|———–|——-| | Sergey Petrov | Physics | 5 | | Ilya Krasnov | Literature | 3 | | Ilya Dolgov | Physics | 3 | | Ilyas Mukhameddinov | History | 4 | | Vera Krpikova | Geography | 4 | | Tatiana Govorova | History | 5 |

Thus, in this lesson we got acquainted with a powerful tool of the SQL language – searching for strings by pattern.

Take tests to reinforce this here and here. If you have any questions, write comments and contact telegram.