totn MariaDB

MariaDB: LIKE Condition

This MariaDB tutorial explains how to use the MariaDB LIKE condition to perform pattern matching with syntax and examples.

Description

The MariaDB LIKE condition allows wildcards to be used in the WHERE clause of a SELECT, INSERT, UPDATE, or DELETE statement. This allows you to perform pattern matching.

Syntax

The syntax for the LIKE condition in MariaDB is:

expression LIKE pattern [ ESCAPE 'escape_character' ]

Parameters or Arguments

expression
A character expression such as a column or field.
pattern

A character expression that contains pattern matching. The patterns that you can choose from are:

Wildcard Explanation
% Allows you to match any string of any length (including zero length)
_ Allows you to match on a single character
escape_character
Optional. It allows you to test for literal instances of a wildcard character such as % or _. If you do not provide the escape_character, MariaDB assumes that "\" is the escape_character.

Example - Using % wildcard (percent sign wildcard)

The first MariaDB LIKE example that we will look at involves using the % wildcard (percent sign wildcard).

Let's explain how the % wildcard works in the MariaDB LIKE condition. We want to find all of the sites whose site_name begins with 'Tech'.

SELECT site_name
FROM sites
WHERE site_name LIKE 'Tech%';

You can also using the % wildcard multiple times within the same string. For example,

SELECT site_name
FROM sites
WHERE site_name LIKE '%OnThe%';

In this MariaDB LIKE condition example, we are looking for all sites whose site_name contains the characters 'OnThe'.

Example - Using _ wildcard (underscore wildcard)

Next, let's explain how the _ wildcard (underscore wildcard) works in the MariaDB LIKE condition. Remember that _ wildcard is looking for only one character.

For example:

SELECT site_name
FROM sites
WHERE category LIKE 'T_ch';

This MariaDB LIKE condition example would return all sites whose category is 5 characters long, where the first character is 'T' and the last two characters is 'ch'. For example, it could return sites whose category is 'Tach', 'Tech', 'Tich', 'Toch', etc.

Example - Using NOT Operator

Next, let's look at how you would use the NOT Operator with wildcards in MariaDB.

Let's use the % wilcard with the NOT Operator. You could also use the MariaDB LIKE condition to find sites whose site_name does not start with 'Check'.

For example:

SELECT site_name
FROM sites
WHERE site_name NOT LIKE 'Check%';

By placing the NOT Operator in front of the MariaDB LIKE condition, you are able to retrieve all sites whose site_name does not start with 'Check'.

Example - Using Escape Characters

It is important to understand how to "Escape Characters" when pattern matching. These examples deal specifically with escaping characters in MariaDB.

Let's say you wanted to search for a % or a _ character in the MariaDB LIKE condition. You can do this using an Escape character.

Please note that you can only define an escape character as a single character (length of 1).

For example:

SELECT *
FROM sites
WHERE password LIKE '%\%';

Since we didn't specify an escape character, MariaDB assumes that the "\" is the escape character. MariaDB then assumes that the escape character is "\" which results in MariaDB treating the second % character as a literal instead of a wildcard. This statement would then return all sites whose password ends with the % character as a literal.

We can override the default escape character in MariaDB by providing the ESCAPE modifier as follows:

SELECT *
FROM sites
WHERE password LIKE '%!%' ESCAPE '!';

This MariaDB LIKE condition example identifies the ! character as an escape character. The ! escape character would result in MariaDB treating the % character as a literal. As a result, this statement will also return all sites whose password ends with the % character.

Here is another more complicated example using escape characters in the MariaDB LIKE condition.

SELECT *
FROM sites
WHERE password LIKE 'P%\%';

This MariaDB LIKE condition example returns all sites whose password starts with P and ends with the % character. For example, it would return a value such as 'Programming%'. Since we did not specify an escape character in the LIKE condition, MariaDB assumes that the escape character is "\" which results in MariaDB treating the second % character as a literal instead of a wildcard.

We could modify this LIKE condition by specfying an escape character as follows:

SELECT *
FROM sites
WHERE password LIKE 'P%!%' ESCAPE '!';

This MariaDB LIKE condition example returns all sites whose password starts with P and ends in the literal %. For example, it would return a value such as 'Programming%'.

You can also use the escape character with the _ character in the MariaDB LIKE condition.

For example:

SELECT *
FROM sites
WHERE password LIKE 'P%\_';

Again, since no ESCAPE modifier is provided, MariaDB uses "\" as the escape character resulting in the _ character to be treated as a literal instead of a wildcard. This example would then return all sites whose password starts with P and ends in _. For example, it would return a value such as 'Programming_'.