MySQL/MariaDB Full Text Search - LIKE Operator

Colleagues hello to all.

In today's article, we'll talk about how to use the LIKE operator in MySQL and MariaDB in your SQL queries.

Mostly, the LIKE operator is used in cases where you have organized a search on your site. Another very important point is that the LIKE operator applies only to fields of the CHAR and VARCHAR types, if you apply the LIKE operator to the INT type, for example, the results will be unpredictable.

The LIKE operator is used in a clause together with the WHERE clause to search for a given substring in a column. For a better substring search, so-called patterns are used. Patterns are special characters that can stand for anything. There are only three template options. The first pattern is the percent sign (%), the second underscore (_), and the third is square brackets ([ ]).

Template symbols:

• ( _ ) - Matches any single character.
• ( % ) - Any number of characters of arbitrary length.
• ( [ ] ) - A sequence of characters. If, for example, we specify characters DK, then the search will be performed taking into account that the information will begin with D or K.

Syntax for the LIKE operator:

SELECT column1, column2, ... FROM table_name WHERE columnN LIKE [pattern]string[pattern];

• SELECT column1, column2 - The name of the fields from which we want to take the values.
• FROM table_name - The name of the table we want to get values ​​from.
• WHERE columnN - The name of the field to be searched for.
• LIKE [pattern]string[pattern]  - The substring that we will compare with the strings of the field columnN.

 [pattern] - Optional.

To check how the LIKE operator works, we will be on a table that I created called it_inzhener_like and this table will have only two fields, an id field and a title field.

And in this table, I added a couple of lines on which we will check how the LIKE operator works.

Examples of using the LIKE operator:

In the first example, we'll use a pattern to search for the percent sign (%), and we'll look for articles that start with the letter M followed by any number of characters.

sql> SELECT title FROM inzhener.it_inzhener_like WHERE title LIKE 'M%';

If we didn't use the percent sign (%) pattern, we would end up with zero records.

Now let's find the articles that end with the letters er, and we'll use the same pattern percentage sign (%).

sql> SELECT title FROM inzhener.it_inzhener_like WHERE title LIKE '%er';

Now let's check out the one you'll be using on your site. We will look for articles that contain a MySQL substring followed by and before this substring and followed by any number of characters. We will use the same template percent sign (%), but we will insert this template at the beginning and end of the substring.

sql> SELECT title FROM inzhener.it_inzhener_like WHERE title LIKE '%mysql%';

As you can see, as a result, we got the title of an article that contains the word MySQL at the beginning of the line and in the middle.

To organize a search on your site, this option is more suitable than the ones I showed above.

Thank you all, I hope that my article helped you in some way.