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 −

S.No WildCard & Definition

1

%

The percent sign represents zero, one or multiple characters.

2

_

The underscore represents a single number or character.

3

[]

This matches any single character within the given range in the [].

4

[^]

This matches any single character excluding the given range in the [^].

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 −

ID NAME AGE ADDRESS SALARY
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

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 −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00

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 −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
7 Muffy 24 Indore 10000.00

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 −

ID NAME AGE ADDRESS SALARY
1 Ramesh 32 Ahmedabad 2000.00
4 Chaitali 25 Mumbai 6500.00
5 Hardik 27 Bhopal 8500.00
7 Muffy 24 Indore 10000.00

Example

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

SELECT 'Tutorialspoint' LIKE 'Tutorialspoint';

Following is the output −

'Tutorialspoint' LIKE 'Tutorialspoint'
1

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

SELECT 'Tutorialspoint' LIKE 'Tutorial';

Following is the output −

'Tutorialspoint' LIKE 'Tutorial'
0

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 −

NULL LIKE 'value'
NULL

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

SELECT 'Tutorialspoint' LIKE NULL;

Following is the output −

'Tutorialspoint' LIKE NULL
NULL

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 −

To fetch records from a table, based on a specified pattern through PHP program, we need to execute the 'SELECT' statement with 'LIKE' clause using the mysqli function query() as follows −

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

To fetch records from a table, based on a specified pattern through Node.js program, we need to execute the 'SELECT' statement with 'LIKE' clause using the query() function of the mysql2 library as follows −

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

To fetch records from a table, based on a specified pattern through Java program, we need to execute the 'SELECT' statement with 'LIKE' clause using the JDBC function executeUpdate() as follows −

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

To fetch records from a table, based on a specified pattern through Python program, we need to execute the 'SELECT' statement with 'LIKE' clause using the execute() function of the MySQL Connector/Python as follows −

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();

Output

The output obtained is as follows −

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)
  });
});

Output

The output produced is as follows −

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();
		}
	}
}

Output

The output obtained is as shown below −

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()

Output

Following is the output of the above code −

(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))
Advertisements