MariaDB LIKE Operator [7 Examples]

In this MariaDB tutorial, we will learn about the MariaDB LIKE operator using some examples. And we will also cover the following topics.

• MariaDB like query
• MariaDB like regex
• MariaDB like case sensitive
• MariaDB like case insensitive
• MariaDB like example
• MariaDB like concat
• MariaDB like number

MariaDB LIKE Query

The LIKE operator is a logical operator in MariaDB that returns TRUE when any string matches with the specific pattern provided with the LIKE operator. In other words, with the help of a like operator, we can test whether string or expression matches the pattern.

If string or expression matches with the pattern, then it returns TRUE otherwise FALSE. The pattern is used with the LIKE operator, and this pattern is called wildcards. Two wildcards are used with the like operator in MariaDB.

• Percent(%): It is called percent wildcard that matches any string with any number of characters.
• Underscore(_): It is called an underscore wildcard that matches any single character.

The syntax of LIKE is given below.

expression LIKE pattern

LIKE operator is used with WHERE clause of the SELECT, UPDATE, DELETE statements.

Let’s understand with an example, here the will run query containing the LIKE operator.

SELECT country,`Item Type` FROM sales
WHERE `Item Type` LIKE 'b%';

This is an example of a MariaDB LIKE Query.

Also, check: MariaDB query examples

MariaDB LIKE Regex

We know about the LIKE operator from the above sub-section, but we can achieve the same result using the REGEXP, which is used for matching against the regular expression. It performs case-insensitive matching against the expressions.

The syntax is given below.

expression REGEXP pattern

The values of pattern are one of the following:

Description	Value
For matching the end of a string.	$
An asterisk matches with zero or more than zero occurrences.	*
Plus matches with one or more than zero occurrences.	+
Question Mark matches with zero or one occurrence.	?
Dot matches any character excluding NULL.	.
It represents the “OR” operator to use more than one expression.	|
It is used to match the any of characters in the list and the symbol represent the list of characters.	[ ]
It matches any character instead of the ones that are specified in the list.	[^]
Group expressions are sub-grouped using this operator.	( )
It matches with j number of times.	{j}
It matches with not less than j number of times.	{j,}
It matches with not less than j number of times, but at most k number of times.	{j,k}
k represents the number within the range of 1 and 9 and matches the kth sub-expression that exists within ( ) before it reaches \k.	\k
It matches one collation element that has more than one character.	[. .]
It matches with character classes.	[: :]
It matches with equivalence classes.	[==]
It matches with a digit character.	\d
It matches with a non-digit character.	\D
It matches with a word character.	\w
It matches with a non-word character.	\W
It matches with a whitespace character.	\s
It matches with a non-whitespace character.	\S
It matches with the previous pattern of zero or more than zero occurrences.	*?
It matches with the previous pattern of one or more than zero occurrences.	+?
It matches with the previous pattern zero or more than one occurrence.	??
It matches with the previous pattern k number of times.	{k}?
It matches with the previous pattern not less than k number of times.	{k,}?
It matches with the previous pattern not less than k number of times, but at most j number of times.	{k,j}

Let’s show the name of the country that starts with ‘A’.

SELECT country  FROM sales
WHERE country REGEXP '^A';

Read: How to create a database from an SQL file in MariaDB

MariaDB LIKE case sensitive

In MariaDB, Collate is used to make the LIKE operator act as case sensitive while matching the pattern again string. Collate is a kind of rule that instructs MariaDB to store and sort the character data in the database.

In the above section, we have performed the case insensitive matching against the string where the case of the letter doesn’t matter like upper and lowercase.

Whether using lower ‘c‘ or upper ‘C‘ both is the same in the case of case-insensitive but in the case of case-sensitive both is different.

Let’s use the collation for case insensitive.

SELECT `Item Type`, `Units Sold` FROM sales
WHERE `Item Type` collate latin1_general_cs LIKE 'c%';

In the above code, In the WHERE clause after column ‘Item Type', the keyword COLLATE is used to specify the collation ( latin1_general_cs ) for case-sensitive.

Before running the above code, let’s at the column Item Type of table sales.

SELECT `Item Type` FROM sales;

The Item Type the column contains the varchar kind of data and starting letter of each item of the column is in Capital.

When we run the above code, we get nothing because of the pattern ‘c%‘ where the letter ‘c‘ is in small letters but in the Item Type column, every item starts with a capital letter. Let’s again run the same code with little change from small ‘c‘ to capital ‘C‘.

SELECT `Item Type`, `Units Sold` FROM sales
WHERE `Item Type` collate latin1_general_cs LIKE 'C%';

Now in the above code after the LIKE in the pattern capital ‘C’ is used.

Read: MariaDB Insert Into

MariaDB LIKE case insensitive

Although the MariaDB LIKE operator is case insensitive by default while matching the pattern against the string, we can also make this operator act like case insensitive using the LOWER function. This LOWER function converts any string from upper case to lower case.

First, let’s know about “What is case insensitive“. Case insensitive means there is not any difference between upper and lowercase letters, both are the same.

SELECT `Item Type`, `Units Sold` FROM sales
WHERE LOWER(`Item Type`) LIKE 'c%';

Here, we have checked an example of MariaDB LIKE case insensitive.

Read: MariaDB create procedure

MariaDB LIKE Example

As we have already talked about the LIKE operator and wildcards in the first sub-section of this tutorial, here we will do the different examples of the LIKE operators with wildcards such as underscore(_) and percentage sign (%).

Find the value that starts with ‘b%’.

SELECT country  FROM sales
WHERE country LIKE  'b%';

Find the value that ends with ‘%e’.

SELECT country  FROM sales
WHERE country LIKE  '%e';

Find the value that has ‘e’ in any position ‘%e%’.

SELECT country  FROM sales
WHERE country LIKE  '%e%';

Find the value that has ‘e’ in the second position ‘_e%’.

SELECT country  FROM sales
WHERE country LIKE  '_e%';

Find the values that start with ‘e’ and are at least two characters in length ‘e_%’.

SELECT country  FROM sales
WHERE country LIKE  'e_%';

Find the values that start with ‘k’ and are at least three characters in length ‘k__%’.

SELECT country  FROM sales
WHERE country LIKE  'k__%';

Find the values that start with ‘u’ and end with ‘a’ ‘u%a’.

SELECT country  FROM sales
WHERE country LIKE  'u%a';

In the above output, at line number 1, we are accessing the country name column from a sales table using the SELECT statement.

In the second line or line number 2, Using the WHERE clause we are filtering the country name, also using the LIKE operator only filtering for specific country names that start with u and end with a.

The output shows the name of the country that starts with u and ends with a is United States of America and Uganda.

Read: How to Change Column in MariaDB

MariaDB LIKE Concat

In MariaDB, the pattern for the LIKE operator can be specified using the CONCAT function, the CONCAT function add two or more string into one string.

The syntax of CONCAT is given below.

CONCAT(string_1,string_2);

Let’s understand with an example.

SELECT CONCAT('United','States');

In the above code, the Concat function contains two strings United and States separated by comma (,). The contact function will combine these strings into one string as UnitedStates. see the output given below.

Now, we will provide the pattern in the form of a string using the Concat function.

Let’s find the name of the country that contains the ‘i’ letter.

SELECT country FROM sales
WHERE country LIKE CONCAT('%','i','%');

In the above code, the name of the country is selected using the SELECT statement from the table sales. Then filtering the country names using the WHERE clause, this WHERE clause contains the LIKE operator with CONCAT function as a pattern to match the name of the country that contains the letter ‘i‘.

The output shows the name of countries like the United Kindom, United States of America that contains the letter ‘i’.

This is how the Concat function is used with the LIKE operator in MariaDB.

Read: MariaDB Update Statement

MariaDB LIKE Number

Till now, we have used the LIKE operator to match against the string, now we will use the LIKE operator to match against the number.

Let’s find the number of units sold for each item type, and the units start with the number 9.

SELECT `Item Type`, `Units Sold` FROM sales
WHERE `Units Sold` LIKE '9%';

So here in the above code, viewing the columns Item Type and Units Sold from a table sales using the SELECT statement. Then filter the result based on Units Sold using the LIKE operator in the WHERE clause. ‘9%‘ means show the Units Sold that start with 9.

As from the above output, we can each item type sold in units that start with the number 9.

Here we saw an example of MariaDB LIKE Number.

You may also like to read the following MariaDB tutorials.

• How to Remove User in MariaDB
• How to Grant All Privileges in MariaDB
• MariaDB Vs SQLite
• MariaDB import CSV
• How to Grant User Access to a MariaDB Database
• MariaDB Delete Row

So, in this tutorial, we have learned about the MariaDB LIKE operator and also covered the following topics.

• MariaDB like query
• MariaDB like regex
• MariaDB like case sensitive
• MariaDB like case insensitive
• MariaDB like example
• MariaDB like concat
• MariaDB like number