How to use Like Operator in MySQL and MariaDB - MySQL Developer Tutorial

How to use Like Operator in MySQL and MariaDB

LIKE logical operator is used when we want to return the row if operand matches a pattern. Like operator returns TRUE if the operand matches a pattern.


Sometime we need to perform pattern matching instead of equal or not equal. Like is used when we want to return the row if specific character string matches a specified pattern. Pattern can be combination of regular characters and wildcard characters.
To return the row back, regular characters must exactly match the characters specified in the character string.The wildcard characters can be matched with arbitrary parts of the character string.

Let's create Customer table and then create some real time examples


Create table Customer
 (Id int,
  FName VARCHAR(50),
  LName VARCHAR(50),
  CountryShortName CHAR(2));

insert into Customer
Values (
1,'Raza','M','PK'),
(2,'Rita','John','US'),
(3,'Sukhi','Singh',Null),
(4,'James','Smith','CA'),
(5,'Robert','Ladson','US'),
(6,'Alice','John','US'),
(7,'Raza','M','US'),
(8,'Dita','M','US'),
(9,'Adita','M','US');



1) Using %
Let's say if we want to find all the rows where FName contains "i" in them. We can use below query


Select * From Customer
where FName like '%i%';



How to use % with Like Operator in MySQL














Noticed that by using % before and after "i", we are telling the query to find all rows in which FName has "i" character and does not matter what other characters are before and after "i".

2) Using _ (underscore)
The underscore can be used when we want to check single character that can be anything and provide the rest of the characters for our match. Let's say that if I want to find all rows where FName first character can be anything but rest of them should  be "ita". I can use below query.


Select * From Customer
where FName like '_ita';



How to use underscore( _ ) with Like Operator in MySQL and MariaDB

3)  Using [ ] - Any single character within the specified rang  [a-t] or set [abc]
Like operator with [ ] can be used when we want to have range. Let's say if I want to find all the rows where FName first character start with [a-f]. We can use below query.


Select * From Customer
where FName like '[a-f]%';


How to use Rang with Like operator in MySQL or MariaDB

As  you can see that I have used [a-f]%. That means I want the first character from a to f and after that any characters are fine as I used %.

4) [^]   Any single character NOT within the specified rang  [a-t] or set [abc]
Let's say if I want to find all the rows where FName first character Dost NOT start with [a to f]. We can use below query.


Select * From Customer where FName like '[^a-f]%';
How to use Not in Range with Like Operator in MySQL or MariaDB


Noticed that it only returned us the rows which does not start with any character from a-f.


Let's say that if we are want to get all the rows where FName does not start with a,d,j. we can use below query.


Select * From Customer
where FName like '[^adj]%';



MySQL or MairaDB Tutorial for Beginners 



1 comment:

  1. How to use Like parameter for MySQL queries, I know that we can use ? for using = but how to use like
    e.g
    where test1 = ?

    what to use for - where test1 like ?

    ReplyDelete