MySQL Like & Not Like Wildcard Operator With Example

MySQL Like & Not Like clause with query example; In this tutorial, you will learn how to use the LIKE & NOT LIKE operator using wildcards characters with help of example.

If you want to fetch data using some condition, you can use LIKE & NOT LIKE condition operator. You can also use MySQL LIKE & NOT LIKE operator with wildcards characters and MySQL Joins, Logical operators, MySQL clauses.

Wildcards are characters that help search data matching complex criteria. wildcards are used in conjunction with the LIKE comparison operator or the NOT LIKE comparison operator.

MySQL Like & Not Like Wildcard Operator With Example

• 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.

LIKE Operator Syntax

The syntax of the MySQL LIKE function is:

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

Before take example of MySQL Like & NOT Like condition, we need to create a table name users and insert data into this table.

First, we need to 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)
 );

Next, we need to 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');

MySQL LIKE Operator Example

Let’s see example of MySQL LIKE function.

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, we 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.

NOT LIKE Operator Syntax

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

Let’s see example of MySQL NOT LIKE function.

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, we 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