What is MySQL LIKE operator? - Scaler Topics

One of the most widely used Relational Database Management Systems (RDBMS) in use today is MySQL. It offers a variety of tools and functionalities for data management and is frequently used for online applications. The ability of MySQL to search for data using the LIKE operator is among its most helpful capabilities. A MySQL table column can be searched for specific patterns using LIKE in MySQL. The LIKE in MYSQL can be applied to a string to match a single character, a group of characters, or both.

A character or string is compared with the pattern supplied by LIKE in MySQL. To match the data, the LIKE in MySQL uses two wildcard characters: the percentage (%) and the underscore ( _ ) characters. The underscore ( _ ) character matches a single character, while the percentage (%) character matches any number of characters (zero or more) in the string.

Syntax

Parameters

1. escape*character:
   It is not mandatory. You can use it to check for literal occurrences of wildcard characters like % or *. If you don't specify the escape character, MySQL will presume that it is \.
2. pattern:
   An expression for characters that uses pattern matching.

MySQL LIKE Operator Examples

Think of a table called students that has the columns rollno, name, age, and email.

students Table:

The LIKE in MySQL can be used as demonstrated below to look for a particular pattern in the table's name column:

MySQL LIKE query:

Output:

Explanation:

Using this query, all rows from the students table whose name column begins with the letter S will be chosen. The query's wildcard character, %, matches any number of characters that come after the letter S in the string.

Using MySQL LIKE Operator with the Percentage (%) Wildcard Examples

Each character in a string (zero or more) can be found using the percentage (%) wildcard. Take a look at the following examples:

Example - 1:

MySQL LIKE query using %:

Output:

Explanation:

Using this query, all entries from the students table whose name column contains the word Shi will be chosen. After the string Shi, the wildcard character % matches any number of characters.

Example - 2:

MySQL LIKE query using %:

Output:

Explanation:

With this query, all rows from the students table whose email column ends in the string @outlook.com will be chosen. Any amount of characters before the string @outlook.com are acceptable with the wildcard character %.

Example - 3:

MySQL LIKE query using %:

Output:

Explanation:

This search will pick out every row from the students table whose name column contains the string hi. Any amount of characters can be used before or after the string hi using the wildcard character %.

Using MySQL LIKE Operator with Underscore ( _ ) Wildcard Examples

Any single character in a string can be matched with the underscore (_) wildcard. Take a look at the following examples:

Example - 1:

MySQL LIKE query using _:

Output:

Explanation:

This search will pull out every row in the students table where the name column begins with the string Shiv followed by any single character, and then the letter m.

Example - 2:

MySQL LIKE query using _:

Output:

Explanation:

With this query, all entries from the students table whose email column begins with any single character and ends with the string @outlook.com will be chosen.

Example - 3:

MySQL LIKE query using _:

Output:

Explanation:

The students table will be searched for any rows with the string hiva in the name column. A single character is checked with the wildcard _ character before and after the string hiva.

Using MySQL NOT LIKE Operator Example

To find data that does not match the supplied pattern, use the NOT LIKE operator. Take a look at the following examples:

Example - 1:

MySQL Query:

Output:

Explanation:

The students table will be searched for all rows with a name column that does not begin with the string S. The LIKE in MySQL is negated with the keyword NOT.

Example - 2:

MySQL Query:

Output:

Explanation:

The students table will be searched for all rows with a name column that does not begin with the string Shiv followed by any single character.

MySQL LIKE Operator with the ESCAPE Clause

We may occasionally need to look for information in strings that themselves contain wildcard characters. In these circumstances, we can combine the LIKE in MySQL and the ESCAPE clause. The wildcard characters in the string are escaped using the escape character specified in the ESCAPE clause. Take a look at the following examples:

Example - 1:

MySQL LIKE query using ESCAPE clause:

Output:

Explanation:

The students table will be searched for all rows with name columns. In MySQL \ is identified as the default escape character and hence, it is not required to specify it using the ESCAPE clause. The \ escape character would result in MySQL treating the _ character as a literal, instead of a wildcard character. As a result, this statement will return all rows which contain _ in the name column.

Example - 2:

MySQL LIKE query using ESCAPE clause:

Output:

Explanation:

The students table will be searched for all rows with name columns. Here the ! character is considered an escape character as we have mentioned it using the ESCAPE clause. The % character would be treated as a literal by MySQL as ! is used to escape it. The query would return all rows which contain S followed by % and after that any number of characters in the name column.

Using the LIKE Clause at the Command Prompt

The LIKE clause can also be used to search for information in a MySQL table at the command prompt. Take a look at the following examples:

Example - 1:

MySQL LIKE query for command prompt:

Output:

Explanation:

The students table's name field must begin with the string Shiv for this command to select that row's information. The command's wildcard character % matches any number of characters that come after the string Shiv.

Example - 2:

MySQL LIKE query for command prompt:

Output:

Explanation:

The students table's name field must begin with the string Shiv, followed by any single character, and then followed by the character m. The command's wildcard character _ matches a single character that comes after the string Shiv and before the letter m.