MySQL - LIKE OPERATOR
The MySQL LIKE operator is used to search for a specified pattern within a column's values. It is particularly helpful for performing partial matches and pattern matching, especially when dealing with string data.
Wildcards:
- % : Matches zero, one, or multiple characters.
- _ : Matches exactly one character.
Syntax
SELECT column1,column2,column3... FROM table_name where column_name LIKE pattern;
Demo Database:
- Table Name : products
| product_id | product_name | product_code | price | unit | category_id |
|---|---|---|---|---|---|
| 1 | The Psychology | 7550 | 250.00 | Piece | 1 |
| 2 | Stories for Children | 3098 | 350.00 | Piece | 1 |
| 3 | Harry Potter | 8472 | 275.00 | Piece | 1 |
| 4 | Tecno Spark | 9468 | 8000.00 | Nos | 2 |
| 5 | Samsung Galaxy | 7188 | 10000.00 | Nos | 2 |
| 6 | Panasonic Eluga | 3433 | 7000.00 | Nos | 2 |
| 7 | Lenovo IdeaPad | 6708 | 45000.00 | Nos | 3 |
| 8 | ASUS Celeron Dual Core | 3583 | 43000.00 | Nos | 3 |
MySQL Like Examples
1) The statement selects all units starting with "N" in product table:
Example
SELECT * FROM products where unit LIKE 'N%';Try it Yourself
2) The statement selects all units ending with "e" in product table:
Example
SELECT * FROM products where unit LIKE '%e';Try it Yourself
3) The statement selects all the product names contains "y":
Example
SELECT * FROM products where product_name LIKE '%y%';Try it Yourself
4) The statement selects all the product names contains the "o" at the second character:
Example
SELECT * FROM products where product_name LIKE '_o%';Try it Yourself
5) The statement selects all the product names starts with "c" and ends with "t":
Example
SELECT * FROM products where product_name LIKE 'c%t';Try it Yourself
6) The statement selects all the product names contains full name of unit "Nos":
Example
SELECT * FROM products where unit LIKE 'Nos';Try it Yourself