MySQL LIKE() function

In this context, we will learn how we can use the MySQL LIKE() function with proper syntax and good examples.

Introduction of MySQL LIKE() function

The LIKE function is a logical function that verifies whether a string contains a mentioned pattern or not.

Syntax of the MySQL LIKE() function

The syntax of the MySQL LIKE() function is given as follows:

expression LIKE pattern ESCAPE escape_character

If the expression matches the pattern in this syntax, the LIKE function returns 1. Otherwise, it returns 0.

MySQL provides two wildcard characters for constructing patterns: percentage % and underscores _.

• The percentage ( % ) wildcard matches any string of zero or more characters.
• The underscore ( _ ) wildcard matches any single character.

For example, s% matches any string that starts with the character s, such as sun and six. The se_ matches any string starts with se and is followed by any character such as see and sea.

When the pattern contains the wildcard character, and you want to treat it as a regular character, you can use the ESCAPE clause.

Typically, you'll use the LIKE function in the WHERE clause of the SELECT, DELETE, and UPDATE statement.

Returns:

It will return whether a string contains a specified pattern or not.

Application used for LIKE() function:

The LIKE() function can be used in the given below MySQL versions.:

• MySQL 5.7
• MySQL 5.6
• MySQL 5.5
• MySQL 5.1
• MySQL 5.0
• MySQL 4.1
• MySQL 4.0
• MySQL 3.23

Examples of MySQL LIKE() function:

Now we will look into some MySQL LIKE() function examples and will explore how we can use the LIKE function in MySQL.

We will use the following worker table from the sample database for the demonstration:

Worker Table

Worker

Workernumber

Firstname

Lastname

Email

Extension

Email

reportsTo

Officecode

jobname

A) Using MySQL LIKE function with the percentage (%) wildcard examples

This example uses the LIKE function to find workers whose first names start with the letter s:

SELECT
employeeNumber,
Lastname,
firstName
FROM
worker
WHERE
firstName LIKE 'a%';

OUTPUT:

Workernumber	Firstname	Lastname
1656	Sasmita	Sahoo
1687	Suman	sahoo

In this example, MySQL scans the whole worker table to find workers whose first names start with the letter ‘s’ and are followed by any number of characters.

The following query uses the LIKE function to find workers whose last names end with the literal string on, e.g., Patterson, Thompson:

SELECT
employeeNumber,
Lastname,
firstName
FROM
worker
WHERE
Lastname LIKE '%on';

Here, we used the percentage (%) wildcard at the beginning and the end of the substring to check if a string contains a substring or not.

For example, the following query uses the LIKE function to find all workers whose last names contain the substring on:

SELECT
employeeNumber,
Lastname,
firstName
FROM
worker
WHERE
lastname LIKE '%on%';

B) Using MySQL LIKE function with an underscore( _ ) wildcard examples

To find workers whose first names start with the letter T, end with the letter m, and contain any single character between, e.g., Tom, Tim, you use the underscore (_) wildcard to construct the pattern as follows:

SELECT
employeeNumber,
Lastname,
firstName
FROM
worker
WHERE
firstname LIKE 'T_m';

C) Using MySQL NOT LIKE function example

The MySQL allows you to combine the NOT function with the LIKE function to find a string that does not match a specific pattern.

Suppose you want to search for workers whose last names don't start with the letter B; you can use the NOT LIKE function as follows:

SELECT
employeeNumber,
Lastname,
firstName
FROM
worker
WHERE
lastName NOT LIKE 'B%';

It should be noted that the pattern is not case-sensitive. Therefore, the b% and B% patterns return the same result.

MySQL LIKE function with the ESCAPE clause

Sometimes the pattern may contain wildcard characters, e.g., 10%, _20, etc.

In this case, you can use the ESCAPE clause to specify the escape character so that the LIKE function interprets the wildcard character as a literal character.

If you don't specify the escape character explicitly, the backslash character (\) is the default escape character.

For example, if you want to find products whose product codes contain the string _20, you can use the pattern %\_20% with the default escape character:

SELECT
productCode,
productName
FROM
products
WHERE
productCode LIKE '%\_20%';

Alternatively, you can specify a different escape character, e.g., $ using the ESCAPE clause:

SELECT
productCode,
productName
FROM
products
WHERE
productCode LIKE '%$_20%' ESCAPE '$';

The pattern %$_20% matches any string that contains the _20 string.

Application of MySQL LIKE() function:

This function is used to test whether a string contains a specified pattern or not.

Summary

In the above context, we have learned how we can use the LIKE() function in MySQL

to Use the LIKE function to test if a value matches a pattern.

The % wildcard matches zero or more characters.

The _ wildcard matches a single character.

Use ESCAPE clause specifies an escape character other than the default escape character (\).

Use the NOT function to negate the LIKE function.