MySQL LIKE Keyword
The MySQL LIKE keyword is used in a WHERE clause to search for a specified pattern in a specified column. The wildcards which are used in conjunction with the LIKE keyword are given below:
Note: The NOT LIKE keyword is the negation of LIKE keyword.
Wildcard Characters in MySQL
Symbol | Description | Example |
---|---|---|
% | Represents zero, one or multiple characters. | 'J%' represents a value that start with "J", for example - John, Jo and Jack etc. |
_ | Represents one character. | '_o%' represents a value that have "o" in the second position, for example - John, Jo and Journey etc. |
Syntax
The syntax for using LIKE keyword in MySQL is given below:
SELECT column1, column2, ... FROM table_name WHERE column LIKE pattern;
The table below describes patterns which is used with LIKE keyword and uses (%) and (_).
Pattern | Description |
---|---|
'J%' | A value that start with "J". |
'%n' | A value that end with "n". |
'%oh%' | A value that have "oh" in any position. |
'_o%' | A value that have "o" in the second position. |
'J_%' | A value that start with "J" and have at least 2 characters. |
'J__%' | A value that start with "J" and have at least 3 characters. |
'J%n' | A value that start with "J" and ends with "n". |
Example:
Consider a database containing a table called Employee with the following records:
EmpID | Name | City | Age | Salary |
---|---|---|---|---|
1 | John | London | 25 | 3000 |
2 | Marry | New York | 24 | 2750 |
3 | Jo | Paris | 27 | 2800 |
4 | Kim | Amsterdam | 30 | 3100 |
5 | Ramesh | New Delhi | 28 | 3000 |
6 | Huang | Beijing | 28 | 2800 |
-
To fetch all records from Employee table where Salary starts with 3 and ends with 0, the query is:
SELECT * FROM Employee WHERE Salary LIKE '3%0';
This will produce the result as shown below:
EmpID Name City Age Salary 1 John London 25 3000 4 Kim Amsterdam 30 3100 5 Ramesh New Delhi 28 3000 -
Similarly, to fetch all records from Employee table where Name of the employee starts with 'Jo', the query is:
SELECT * FROM Employee WHERE Name LIKE 'Jo%';
This will produce the result as shown below:
EmpID Name City Age Salary 1 John London 25 3000 3 Jo Paris 27 2800 -
Using NOT LIKE keyword: NOT LIKE keyword is used as the negation of LIKE keyword. For example, to select all records of the Employee table with Name not starting with 'Jo', the following query can be used:
SELECT * FROM Employee WHERE Name NOT LIKE 'Jo%';
This will produce the result as shown below:
EmpID Name City Age Salary 2 Marry New York 24 2750 4 Kim Amsterdam 30 3100 5 Ramesh New Delhi 28 3000 6 Huang Beijing 28 2800
❮ MySQL Keywords