MySQL Like and Not Like Wildcard Operator

MySQL Like & Not Like wildcard operator; In this tutorial, i am going to show you how to use the LIKE & NOT LIKE operator using wildcards characters with help of examples.

MySQL Like and Not Like Wildcard Operator

See the following mysql like and not like operator with wildcard characters; as follows:

• MySQL LIKE
• MySQL NOT LIKE

MySQL LIKE

To return the rows that match the specified pattern in Mysql table, it can be used as a Like operator with wildcards.

Syntax of MySQL LIKE Operator

The syntax of the MySQL LIKE function is:

 column_name LIKE pattern [ ESCAPE 'escape_character' ];
 OR
 expression LIKE pattern [ ESCAPE 'escape_character' ];

First of all, create a new table named users using the following statement :

CREATE TABLE IF NOT EXISTS users (
     id INT AUTO_INCREMENT PRIMARY KEY,
     name VARCHAR(20) NOT NULL,
     phone VARCHAR(15)
 );

Then insert data into the users table using the following query :

INSERT INTO users(name,phone)
 VALUES('Tommy Hill','(541) 754-3009'),
       ('John Smith','(541) 754-3110'),
       ('Marks','(541) 754-3010'),
       ('Robert','(541) 754-3010'),
       ('Michael','(541) 754-3014'),
       ('Matthew','(541) 754-3015'),
       ('Mark','(541) 754-3065'),
       ('Gelvin','(541) 754-3010'),
       ('Ronald','(541) 754-3040'),
       ('Kelvin Row','(541) 754-3111');
       ('Kevin','(541) 754-3121');

Example of MySQL LIKE Operator

If you want to fetch some users have name start “M” latter. You can use the following query for that:

 SELECT id, name
 FROM users 
 WHERE name LIKE 'm%';

In this tutorial, you have learn how to use MySQL LIKE operator with wildcards character.

   +------------+-----------+-----
   | id        | name           |  
   +------------+-----------+-----
   | 5         | Michael        |
   | 6         | Matthew        | 
   | 10        | Marks          | 
   | 11        | Mark           |
   +------------+-----------+-----

MySQL NOT LIKE

It is used to return the rows that do not match the specified pattern in Mysql table, it can be used as a NOT LIKE operator with wildcards.

Syntax of NOT LIKE Operator

The syntax of the MySQL NOT LIKE function is:

 column_name NOT LIKE pattern [ ESCAPE 'escape_character' ];
 OR
 expression NOT LIKE pattern [ ESCAPE 'escape_character' ];

Example of MySQL NOT LIKE Operator

If you want to fetch some users have name not start “M” latter. You can use the following query for that:

 SELECT id,name
 FROM users 
 WHERE name NOT LIKE 'm%';

In this tutorial, you have learn how to use MySQL NOT LIKE operator with wildcards character.

   +------------+-----------+-----
   | id        | name           |  
   +------------+-----------+-----
   | 2         | John Smith     |
   | 5         | Robert         | 
   | 10        | Gelvin         | 
   | 11        | Ronald         |
   | 12        | Kelvin Row     |
   | 13        | Kevin          |
   +------------+-----------+-----

Recommended MySQL Tutorials