MySQL – LIKE Operator

”;

MySQL LIKE Operator

The LIKE Operator in MySQL database is a logical operator that is used to retrieve the data from a table, based on a specified pattern.

To filter and search for some records in a table, in a very basic way is using a WHERE clause. To elaborate, a WHERE clause with the ”equal to” sign (=) works fine whenever we want search for an exact match. But there may be a requirement where we want to filter out all the results wherever the values in a table have a particular pattern. This can be handled by using a LIKE Operator in a WHERE clause.

The LIKE operator is usually used along with a pattern. However, the placement of this pattern (like at the beginning of the record, or at the ending) is decided using some characters known as wildcards. Without a wildcard character, the LIKE operator is very same as the equal to (=) sign in the WHERE clause.

Syntax

Following is the basic syntax of the LIKE operator in MySQL −

SELECT column_name(s) FROM table_name
WHERE column_name LIKE [condition];

• You can specify any condition using the WHERE clause.

• You can use the LIKE Operator along with the WHERE clause.

• You can use the LIKE Operator in place of the equals to sign.

• When LIKE is used along with % sign then it will work like a meta character search.

• You can specify more than one condition using AND or OR operators.

• A WHERE…LIKE clause can also be used in DELETE or UPDATE SQL commands to specify a condition.

Using LIKE Operator with Wildcards

Wildcards are special characters used in SQL queries to match patterns in the data. Following are the four wildcards used in conjunction with the LIKE operator −

Note− In the LIKE operator, the above wildcard characters can be used individually as well as in combinations with each other. The two mainly used wildcard characters are ”%” and ”_”.

Example

In the following query, we are creating a table named CUSTOMERS using the CREATE statement −

CREATE TABLE CUSTOMERS (
   ID INT NOT NULL,
   NAME VARCHAR (20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2),       
   PRIMARY KEY (ID)
);

The below query inserts 7 records into the above-created table −

INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY) VALUES 
(1, ''Ramesh'', 32, ''Ahmedabad'', 2000.00 ),
(2, ''Khilan'', 25, ''Delhi'', 1500.00 ),
(3, ''Kaushik'', 23, ''Kota'', 2000.00 ),
(4, ''Chaitali'', 25, ''Mumbai'', 6500.00 ),
(5, ''Hardik'', 27, ''Bhopal'', 8500.00 ),
(6, ''Komal'', 22, ''Hyderabad'', 4500.00 ),
(7, ''Muffy'', 24, ''Indore'', 10000.00 );

Execute the following query to fetch all the records from the CUSTOMERS table −

Select * From CUSTOMERS;

Following is the CUSTOMERS table −

Now, we are retrieving the name of the customers ending with “esh” using the LIKE operator with wildcards −

SELECT * from CUSTOMERS WHERE NAME LIKE ''%esh'';

Output

The output for the above query is produced as given below −

Using LIKE Operator with AND/OR Operators

In MySQL, we can also use the LIKE operator with multiple string patterns for selecting rows by using the AND or OR operators.

Syntax

Following is the basic syntax of using LIKE operator with AND/OR operator −

SELECT column_name(s)
FROM table_name
WHERE column1 LIKE pattern1 [AND|OR] column2 
LIKE pattern2 [AND|OR] ...;

Example

The following query retrieves the customers whose names start with ”M” and ”R” −

SELECT * FROM CUSTOMERS 
WHERE Name LIKE ''M%'' OR Name LIKE ''R%'';

Output

Following is the CUSTOMERS table −

Using NOT Operator with LIKE Operator

We can use the NOT operator in conjunction with LIKE operator to extract the rows which does not contain a particular string provided in the search pattern.

Syntax

Following is the basic syntax of NOT LIKE operator in SQL −

SELECT column1, column2, ...
FROM table_name
WHERE column1 NOT LIKE pattern;

Example

In the following query, we are retrieving all the customers whose name does not start with K −

SELECT * FROM CUSTOMERS WHERE Name NOT LIKE ''K%'';

Following is the output −

Example

If the search pattern is exactly as provided string, this operator returns 1 −

SELECT ''Tutorialspoint'' LIKE ''Tutorialspoint'';

Following is the output −

If the search pattern is not exactly same as the string, it returns 0 as output −

SELECT ''Tutorialspoint'' LIKE ''Tutorial'';

Following is the output −

Example

If either of the first two operands is NULL, this operator returns NULL.

SELECT NULL LIKE ''value'';

The output for the program above is produced as given below −

Here, we are providing NULL to the seach pattern operand.

SELECT ''Tutorialspoint'' LIKE NULL;

Following is the output −

Client Program

Besides using MySQL LIKE operator to filter and search for some records in a table, based on a specified pattern, we can also use client programs like Node.js, PHP, Java, and Python to achieve the same result.

Syntax

Following are the syntaxes of this operation in various programming languages −

$sql = "SELECT COLUMN_NAME1, COLUMN_NAME2,.. 
   FROM TABLE_NAME WHERE columnn LIKE PATTERN";
$mysqli->query($sql,$resultmode)

sql= "SELECT column_name(s) FROM table_name 
   WHERE column_name LIKE [condition]";
Con.query(sql);

String sql = "SELECT column_name(s) FROM table_name
   WHERE column_name LIKE [condition]";
statement.executeQuery(sql);

like_Operator_query = "SELECT column1, column2, ... 
   FROM table_name WHERE column_name LIKE pattern"
cursorObj.execute(like_Operator_query)

Example

Following are the programs −

$dbhost = ''localhost'';
$dbuser = ''root'';
$dbpass = ''password'';
$dbname = ''TUTORIALS'';
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = ''SELECT * FROM tutorials_tbl Like tutorial_author like "Jo%"'';
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   while($row = $result->fetch_assoc()) {
      printf("Id: %s, Title: %s, Author: %s, Date: %d 
", 
         $row["tutorial_id"], 
         $row["tutorial_title"], 
         $row["tutorial_author"],
         $row["submission_date"]);               
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Id: 4, Title: Learn PHP, Author: John Poul, Date: 2023

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating table
  sql = "CREATE TABLE IF NOT EXISTS tutorials_tbl(tutorial_id INT NOT NULL PRIMARY KEY, tutorial_title VARCHAR(100) NOT NULL, tutorial_author VARCHAR(40) NOT NULL, submission_date DATE);"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO tutorials_tbl(tutorial_id, tutorial_title, tutorial_author, submission_date) VALUES(1, ''Learn PHP'', ''John Paul'', NOW()), (2, ''Learn MySQL'', ''Abdul S'', NOW()), (3, ''JAVA Tutorial'', ''Sanjay'', ''2007-05-21''), (4, ''Python Tutorial'', ''Sasha Lee'', ''2016-09-04''), (5, ''Hadoop Tutorial'', ''Chris Welsh'', NOW());"
  con.query(sql);

  //Using LIKE operator
  sql = "SELECT * from tutorials_tbl WHERE tutorial_author LIKE ''%jay'';"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Connected!
--------------------------
[
  {
    tutorial_id: 3,
    tutorial_title: ''JAVA Tutorial'',
    tutorial_author: ''Sanjay'',
    submission_date: 2007-05-20T18:30:00.000Z
  }
]

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class LikeClause {
	public static void main(String[] args) {
		String url = "jdbc:mysql://localhost:3306/TUTORIALS";
		String user = "root";
		String password = "password";
		ResultSet rs;
		try {
			Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT * from tutorials_tbl WHERE tutorial_author LIKE ''%jay''";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()){
            	String Id = rs.getString("tutorial_id");
            	String Title = rs.getString("tutorial_title");
            	String Author = rs.getString("tutorial_author");
            	String Date = rs.getString("submission_date");
            	System.out.println("Id: " + Id + ", Title: " + Title + ", Author: " + Author + ", Submission-date: " + Date);
            }
		}catch(Exception e) {
			e.printStackTrace();
		}
	}
}

Table records: 
Id: 3, Title: Learning Java, Author: Sanjay, Submission-date: 2007-05-06

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
like_Operator_query = """
SELECT tutorial_id, tutorial_title, tutorial_author, submission_date
FROM tutorials_tbl
WHERE tutorial_title LIKE ''%Tutorial%''
"""
cursorObj.execute(like_Operator_query)
# Fetch all the matching rows
matching_rows = cursorObj.fetchall()
# Printing the matching rows
for row in matching_rows:
    print(row)
cursorObj.close()
connection.close()

(3, ''JAVA Tutorial'', ''Sanjay'', datetime.date(2007, 5, 6))
(4, ''Python Tutorial'', ''Sasha Lee'', datetime.date(2016, 9, 4))
(5, ''Hadoop Tutorial'', ''Chris Welsh'', datetime.date(2023, 3, 28))
(6, ''R Tutorial'', ''Vaishnav'', datetime.date(2011, 11, 4))