LIKE Clause in MariaDB

The LIKE clause is a powerful tool in database querying that allows users to perform flexible and dynamic searches based on pattern matching. In MariaDB, the LIKE clause is a valuable asset for searching and retrieving data from tables. The features, syntax, and effective uses of the LIKE clause in MariaDB will all be covered in this article.

LIKE Clause in MariaDB

The LIKE clause is used in SQL queries to search for a specified pattern in a column. It is particularly useful when you want to retrieve data that matches a certain character pattern or string. The condition word in the above syntax includes wildcard characters. The two main wildcard characters used with the LIKE Clause are:

• “%”: It represents zero or more characters.
• “_”: It matches a single character.

Syntax:

SELECT field1, field2, ... FROM table WHERE column LIKE condition;

Wildcard Character(%)

Use the % wildcard character to specify zero or more characters in a string. To occupy any spectrum, its position can be the beginning, end, or middle of the pattern. The below examples will show you how to use % wildcard character in different ways.

Query 1:

 SELECT first_name FROM employees WHERE first_name LIKE 'J%';

This query will give the list of all employees whose first names start with J.

Output:

LIKE Clause

Query 2:

SELECT first_name FROM employees WHERE first_name LIKE '%J%';

This query will give the list of all employees with first names containing the word J anywhere in the string.

Output:

LIKE Clause

Query 3:

SELECT last_name FROM employees WHERE last_name NOT LIKE 'S%';

This queries will retrieve all the employees whose last name does not start with S.

Output:

LIKE Clause

Wildcard Character(_)

A character in a string is represented by the wildcard character _. It helps when you want to match a character in a specific location. The below examples will give you an idea of how to use _ wildcard character.

Query 1:

SELECT last_name FROM employees WHERE last_name LIKE 'Jo_hn';

This example would return all the employees whose last name is 6 characters long, where the first character is J and last two characters is hn.

Output:

LIKE Clause

Query 2:

SELECT * FROM employees WHERE department_id LIKE '1%' COLLATE utf8mb4_general_ci;

This query will retrieve employees with department_id starting with 1 .

Output:

LIKE Clause

Conclusion

The LIKE clause in MariaDB is a versatile tool for pattern matching, enabling users to craft flexible and precise queries for retrieving data from tables. Whether you need to find records with specific prefixes, suffixes, or patterns within a string, the LIKE clause, coupled with wildcard characters, provides a robust solution. As you become more proficient with thе LIKE clause, you can enhance your ability to search, filter, and analyze data in your MariaDB databases, making it an essential skill for database developers and administrators alike.