MySQL LIKE operator

Create Table

CREATE TABLE cookbook
(
   id INT NOT NULL AUTO_INCREMENT,
   name VARCHAR(20) NOT NULL,
   year INT NOT NULL,
   city VARCHAR(20) NOT NULL,
   code VARCHAR(20) NOT NULL,
   PRIMARY KEY(id)
);

Insert Records

INSERT INTO cookbook(name,year,city,code)
     VALUES('Jimmy',1980,'Greenboro,NC','mx-32cd312'),
           ('Chimmy',2000,'Grambling,LA','mx-32cd300'),
           ('Khom',2000,'Ruston,LA','mx-32cd3001'),
           ('Mark',2010,'Ruston,LA','mx-32cd3001'),
           ('David',2009,'Ruston,LA','mx-32cd3001'),
           ('Joseph',2000,'New Orlean,LA','mx-32cd3011'),
           ('Tommy',2010,'New Orlean,LA','mx-32cd3011'),
           ('Seng',2000,'Galend,Tx','mx-32cd3301'),
           ('Chorn',1999,'Galend,Tx','mx-32cd3301'),
           ('Miny',2000,'Galend,Tx','mx-32cd3301'),
           ('soKat',1998,'Houston,Tx','mx-32cd3801'),
           ('sokha',2001,'Houston,Tx','mx-32cd3801'),
           ('sophea',2002,'Huntsville,Tx','mx-32cd3901'),
           ('sophal',2001,'Houston,Tx','mx-32cd3801');
          

Select records

mysql>select * from cookbook;
+----+--------+------+---------------+-------------+
| id | name   | year | city          | code        |
+----+--------+------+---------------+-------------+
|  1 | Jimmy  | 1980 | Greenboro,NC  | mx-32cd312  | 
|  2 | Chimmy | 2000 | Grambling,LA  | mx-32cd300  | 
|  3 | Khom   | 2000 | Ruston,LA     | mx-32cd3001 | 
|  4 | Mark   | 2010 | Ruston,LA     | mx-32cd3001 | 
|  5 | David  | 2009 | Ruston,LA     | mx-32cd3001 | 
|  6 | Joseph | 2000 | New Orlean,LA | mx-32cd3011 | 
|  7 | Tommy  | 2010 | New Orlean,LA | mx-32cd3011 | 
|  8 | Seng   | 2000 | Galend,Tx     | mx-32cd3301 | 
|  9 | Chorn  | 1999 | Galend,Tx     | mx-32cd3301 | 
| 10 | Miny   | 2000 | Galend,Tx     | mx-32cd3301 | 
| 11 | soKat  | 1998 | Houston,Tx    | mx-32cd3801 | 
| 12 | sokha  | 2001 | Houston,Tx    | mx-32cd3801 | 
| 13 | sophea | 2002 | Huntsville,Tx | mx-32cd3901 | 
| 14 | sophal | 2001 | Houston,Tx    | mx-32cd3801 | 
+----+--------+------+---------------+-------------+
14 rows in set (0.01 sec)

Like operator to match name

mysql> SELECT * FROM cookbook WHERE name LIKE 'Seng';
+----+------+------+-----------+-------------+
| id | name | year | city      | code        |
+----+------+------+-----------+-------------+
|  8 | Seng | 2000 | Galend,Tx | mx-32cd3301 | 
+----+------+------+-----------+-------------+
1 row in set (0.00 sec)

Like operator with not like to do the opposite

mysql> SELECT * FROM cookbook WHERE name NOT LIKE 'Seng';
+----+--------+------+---------------+-------------+
| id | name   | year | city          | code        |
+----+--------+------+---------------+-------------+
|  1 | Jimmy  | 1980 | Greenboro,NC  | mx-32cd312  | 
|  2 | Chimmy | 2000 | Grambling,LA  | mx-32cd300  | 
|  3 | Khom   | 2000 | Ruston,LA     | mx-32cd3001 | 
|  4 | Mark   | 2010 | Ruston,LA     | mx-32cd3001 | 
|  5 | David  | 2009 | Ruston,LA     | mx-32cd3001 | 
|  6 | Joseph | 2000 | New Orlean,LA | mx-32cd3011 | 
|  7 | Tommy  | 2010 | New Orlean,LA | mx-32cd3011 | 
|  9 | Chorn  | 1999 | Galend,Tx     | mx-32cd3301 | 
| 10 | Miny   | 2000 | Galend,Tx     | mx-32cd3301 | 
| 11 | soKat  | 1998 | Houston,Tx    | mx-32cd3801 | 
| 12 | sokha  | 2001 | Houston,Tx    | mx-32cd3801 | 
| 13 | sophea | 2002 | Huntsville,Tx | mx-32cd3901 | 
| 14 | sophal | 2001 | Houston,Tx    | mx-32cd3801 | 
+----+--------+------+---------------+-------------+
13 rows in set (0.00 sec)

Like operator using the percentage sign to make any character

mysql> SELECT * FROM cookbook WHERE name LIKE 's%';
+----+--------+------+---------------+-------------+
| id | name   | year | city          | code        |
+----+--------+------+---------------+-------------+
|  8 | Seng   | 2000 | Galend,Tx     | mx-32cd3301 | 
| 11 | soKat  | 1998 | Houston,Tx    | mx-32cd3801 | 
| 12 | sokha  | 2001 | Houston,Tx    | mx-32cd3801 | 
| 13 | sophea | 2002 | Huntsville,Tx | mx-32cd3901 | 
| 14 | sophal | 2001 | Houston,Tx    | mx-32cd3801 | 
+----+--------+------+---------------+-------------+
5 rows in set (0.00 sec)

Like operator using the underscore sign _ to match single character only

mysql> SELECT * FROM cookbook WHERE name LIKE '_ophal';
+----+--------+------+------------+-------------+
| id | name   | year | city       | code        |
+----+--------+------+------------+-------------+
| 14 | sophal | 2001 | Houston,Tx | mx-32cd3801 | 
+----+--------+------+------------+-------------+
1 row in set (0.01 sec)

Like operator using the underscore sign _ to match two characters

mysql> SELECT * FROM cookbook WHERE name LIKE 'soph__';
+----+--------+------+---------------+-------------+
| id | name   | year | city          | code        |
+----+--------+------+---------------+-------------+
| 13 | sophea | 2002 | Huntsville,Tx | mx-32cd3901 | 
| 14 | sophal | 2001 | Houston,Tx    | mx-32cd3801 | 
+----+--------+------+---------------+-------------+
2 rows in set (0.00 sec)

Like operator to match any character that contains the word so

mysql> SELECT * FROM cookbook WHERE name LIKE '%so%';
+----+--------+------+---------------+-------------+
| id | name   | year | city          | code        |
+----+--------+------+---------------+-------------+
| 11 | soKat  | 1998 | Houston,Tx    | mx-32cd3801 | 
| 12 | sokha  | 2001 | Houston,Tx    | mx-32cd3801 | 
| 13 | sophea | 2002 | Huntsville,Tx | mx-32cd3901 | 
| 14 | sophal | 2001 | Houston,Tx    | mx-32cd3801 | 
+----+--------+------+---------------+-------------+
4 rows in set (0.00 sec)