How to use the LIKE Operator in MySQL8?
The LIKE operator performs pattern matching using an SQL pattern.
If the string matches the pattern given, the result is 1(TRUE), otherwise it’s 0(FALSE).
This is not necessarily to be a literal string. For example, it can be specified as a string expression or table column or numeric expressions.
With LIKE we can use the following two wildcard characters in the pattern:
- % matches any number of characters, even zero characters.
- _ matches exactly one character.
LIKE() : Syntax
LIKE() : Output
[table caption=”” width=”100%” colwidth=”20%|80%” colalign=”left|left”]
Returns,
1 (TRUE), If the string matches the pattern given.
0(FALSE), If the string doesn’t matches the pattern given.
NULL, If either expression or patstring is NULL.
[/table]
LIKE() : Available from
[table caption=”” width=”100%” colwidth=”25%|75%” colalign=”left|left”]
Version, MySQL 4.0
[/table]
LIKE() Example 1 : Basic Usage
Below is an example of how to use this operator in a SELECT statement:
mysql> SELECT 'Tutorialmines' LIKE 'Tut%'; +-----------------------------+ | 'Tutorialmines' LIKE 'Tut%' | +-----------------------------+ | 1 | +-----------------------------+ 1 row in set (0.00 sec)
In Above example, the return value is 1. It means that the input string matched the pattern. We specified that the input string should start with Tut and end with anything. The % wildcard character matches any number of characters (including zero characters).
Below will happens if we drop the wildcard %:
mysql> SELECT 'Tutorialmines' LIKE 'Tut'; +----------------------------+ | 'Tutorialmines' LIKE 'Tut' | +----------------------------+ | 0 | +----------------------------+ 1 row in set (0.00 sec)
In Above example, the return value is 0. It means that the input string doesn’t matched the pattern.
LIKE() Example 2 : The _ ,% Wildcard character
We can use this option also i.e. _ wildcard character. It will match only a one character. Here’s an example:
mysql> SELECT 'Tutorialmines' LIKE 'Tut_rialmines'; +--------------------------------------+ | 'Tutorialmines' LIKE 'Tut_rialmines' | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.00 sec)
We can use _ wildcard character one or more than one times also. Here’s an example:
mysql> SELECT 'Tutorialmines' LIKE 'Tut_____mines'; +--------------------------------------+ | 'Tutorialmines' LIKE 'Tut_____mines' | +--------------------------------------+ | 1 | +--------------------------------------+ 1 row in set (0.00 sec)
Below example, Using both wild characters i.e. _ and % .
mysql> SELECT 'Tutorialmines' LIKE 'Tut_rial%'; +----------------------------------+ | 'Tutorialmines' LIKE 'Tut_rial%' | +----------------------------------+ | 1 | +----------------------------------+ 1 row in set (0.00 sec)
LIKE() Example 3 : Database Example
The LIKE operator is often used within a WHERE clause of a SELECT statement. Below is an example of how we can use this operator within a database query. Below query will fetch all rows which has name starting with charater “p”.
mysql> SELECT name, age from tbl_employee WHERE name LIKE 'p%'; +-------+-----+ | name | age | +-------+-----+ | Polla | 30 | +-------+-----+ 1 row in set (0.00 sec)
LIKE() Example 4 : Escaping with the Backslash Character
To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, \ is assumed.
- \% matches one % character.
- \_ matches one _ character.
What happens if one of the wildcard characters are in your input string and you need to perform a match against it? You can escape it with the backslash character (\).
Lets see the below example.
mysql> SELECT
'Tutorial_mines' LIKE 'Tutorial_mines' AS 'With-out-escape',
'Tutorial_mines' LIKE 'Tutorial\_mines' AS 'With-escape';
+-----------------+-------------+
| With-out-escape | With-escape |
+-----------------+-------------+
| 1 | 1 |
+-----------------+-------------+
1 row in set (0.00 sec))In above case, they both matched, but for different reasons.
- The first line matched, due to the wildcard specified that any character will match.
- The second line also matched, due to an underscore in the right place.
Let’s change the input string slightly so that we get a different result:
mysql> SELECT 'Tutorial_mines' LIKE 'Tutorial_mines' AS 'With-out-escape',
-> 'Tutorial+mines' LIKE 'Tutorial\_mines' AS 'With-escape';
+-----------------+-------------+
| With-out-escape | With-escape |
+-----------------+-------------+
| 1 | 0 |
+-----------------+-------------+
1 row in set (0.00 sec)- The first line unescaped version returned positive, due to the wildcard meant that we could have any character in that spot.
- The second line didn’t matched, escaped version explicitly stated that only the underscore character (_) will match. The input string didn’t have an underscore character in that spot and so the result was negative.
LIKE() Example 5 : The ESCAPE Clause
To specify a different escape character, use the ESCAPE clause. See below example
mysql> SELECT 'Tutorial_mines' LIKE 'Tutorial|_mines' ESCAPE '|' AS Result,
'Tutorial+mines' LIKE 'Tutorial|_mines' ESCAPE '|' AS Result1;
+--------+---------+
| Result | Result1 |
+--------+---------+
| 1 | 0 |
+--------+---------+
1 row in set (0.00 sec)
The escape sequence should be empty or one character long. The expression must evaluate as a constant at execution time. If the NO_BACKSLASH_ESCAPES SQL mode is enabled, the sequence cannot be empty.
LIKE() Example 6 : Numeric Expressions
MySQL permits LIKE on numeric expressions also. See below example
mysql> SELECT 9872 LIKE '9%', 9872 LIKE '98_'; +----------------+-----------------+ | 9872 LIKE '9%' | 9872 LIKE '98_' | +----------------+-----------------+ | 1 | 0 | +----------------+-----------------+ 1 row in set (0.00 sec)
In above case, 9872 match the given pattern,So it returns 1. In second part 9872 LIKE ’98_’, given pattern is set to match 3numbers, while in expression 4numbers are there. so, it returns 0.
LIKE() Example 7 : String comparisons are not case-sensitive
String comparisons are not case-sensitive unless one of the operands is case-sensitive (uses a case-sensitive collation or is a binary string). See below example
mysql> SELECT 'tuts' LIKE 'TUTS'; +--------------------+ | 'tuts' LIKE 'TUTS' | +--------------------+ | 1 | +--------------------+ 1 row in set (0.00 sec) mysql> SELECT 'tuts' LIKE _latin1 'TUTS' COLLATE latin1_general_cs; +------------------------------------------------------+ | 'tuts' LIKE _latin1 'TUTS' COLLATE latin1_general_cs | +------------------------------------------------------+ | 0 | +------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT 'tuts' LIKE _latin1 'TUTS' COLLATE latin1_bin; +-----------------------------------------------+ | 'tuts' LIKE _latin1 'TUTS' COLLATE latin1_bin | +-----------------------------------------------+ | 0 | +-----------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT 'tuts' LIKE BINARY 'TUTS'; +---------------------------+ | 'tuts' LIKE BINARY 'TUTS' | +---------------------------+ | 0 | +---------------------------+ 1 row in set (0.00 sec)
LIKE() Example 8 : NULL arguments
If either expression or patstring is NULL, it will return NULL. See below example
mysql> SELECT 9872 LIKE NULL, NULL LIKE '98_'; +----------------+-----------------+ | 9872 LIKE NULL | NULL LIKE '98_' | +----------------+-----------------+ | NULL | NULL | +----------------+-----------------+ 1 row in set (0.00 sec)
See all MySQL String functions MySQL 8 String Functions.
