How the LIKE operation works in Mariadb?

The LIKE operation is a logical operation that tests whether a string matches a specified pattern. It can be used to perform simple pattern matching on string expressions, such as column values, literal strings, or variables. The LIKE operation can also be used with the NOT operator to negate the result.

Syntax

The syntax of the LIKE operation is as follows:

string_expression LIKE pattern [ESCAPE escape_character]

The string_expression is the string that you want to test against the pattern. It can be any valid string expression in Mariadb.

The pattern is the string that specifies the pattern to match. It can contain two special characters: the percent sign (%) and the underscore (_). The percent sign matches any sequence of zero or more characters, while the underscore matches any single character. For example, the pattern '%a%' matches any string that contains the letter a, while the pattern 'a_b' matches any string that starts with a, ends with b, and has any single character in between.

The ESCAPE clause is optional. It allows you to specify a character that can be used to escape the special characters in the pattern. For example, if you want to match the literal string '%a', you can use the pattern '\%a' and specify the backslash (\) as the escape character. The escape character must be a single character and must not be % or _.

The LIKE operation returns a boolean value that indicates whether the string expression matches the pattern or not. If the string expression or the pattern is NULL, the operation returns NULL.

Examples

In this section, we will show some examples of how to use the LIKE operation in Mariadb.

Example 1: Matching a literal string with a pattern

The following example shows how to use the LIKE operation to test whether a literal string matches a specified pattern.

SELECT 'Hello World!' LIKE '%ello%';

The output is:

1

As you can see, the LIKE operation returns 1, which means true, because the string 'Hello World!' matches the pattern '%ello%', which means any string that contains the substring 'ello'.

Example 2: Matching a column value with a pattern

The following example shows how to use the LIKE operation to test whether a column value matches a specified pattern. Suppose we have a table called customers that stores the customer information, such as name, email, and country. The table has the following data:

We can use the LIKE operation to find the customers whose name starts with A, as shown below:

SELECT id, name, email, country FROM customers WHERE name LIKE 'A%';

The output is:

As you can see, the LIKE operation returns the rows where the name column matches the pattern 'A%', which means any string that starts with A.

Example 3: Matching a variable value with a pattern

The following example shows how to use the LIKE operation to test whether a variable value matches a specified pattern. Suppose we have a variable called @greeting that stores a greeting message, such as “Hello World!”. We can use the LIKE operation to check if the variable value contains the letter o, as shown below:

SET @greeting = 'Hello World!';
SELECT @greeting LIKE '%o%';

The output is:

1

As you can see, the LIKE operation returns 1, which means true, because the variable value 'Hello World!' matches the pattern '%o%', which means any string that contains the letter o.

Example 4: Matching a string with a pattern that contains special characters

The following example shows how to use the LIKE operation to test whether a string matches a pattern that contains special characters, such as % and _. Suppose we have a string that contains some uppercase letters, some lowercase letters, some numbers, some symbols, and some accented characters, such as “A1B2C3!@#$%^&*()ÉÈÊË”. We can use the LIKE operation to find the strings that have a percent sign followed by a letter, as shown below:

SELECT 'A1B2C3!@#$%^&*()ÉÈÊË' LIKE '%\%_';

The output is:

0

As you can see, the LIKE operation returns 0, which means false, because the string 'A1B2C3!@#$%^&*()ÉÈÊË' does not match the pattern '%\%_', which means any string that has a percent sign followed by a single character. Note that we use the backslash (\) as the escape character to indicate that the percent sign in the pattern is a literal character, not a special character.

Related Functions

There are some other functions that are related to the LIKE operation in Mariadb. They are:

• The REGEXP operation: This operation tests whether a string matches a regular expression. It can be used to perform more complex pattern matching on string expressions, such as using character classes, quantifiers, anchors, and modifiers. For example, SELECT 'Hello World!' REGEXP '[A-Z][a-z]+ [A-Z][a-z]+!' returns 1.
• The INSTR() function: This function returns the position of the first occurrence of a substring in a string. It can be used to check if a string contains a substring or not. For example, SELECT INSTR('Hello World!', 'ello') returns 2.
• The SUBSTRING() function: This function extracts a substring from a given string, starting from a specified position and optionally with a specified length. It can be used to get a part of a string that matches a pattern. For example, SELECT SUBSTRING('Hello World!', 2, 4) returns ello.

Conclusion

In this article, we have learned how the LIKE operation works in Mariadb. We have seen the syntax of the operation, and some examples of how to use it with different types of string expressions. We have also learned about some related functions that can be used with the LIKE operation. The LIKE operation is a useful operation that can help us perform simple pattern matching on strings in Mariadb.