MySQL LIKE operator
LIKE operator
MySQL LIKE operator checks whether a specific character string matches a specified pattern.
Syntax:
expr LIKE pat [ESCAPE 'escape_char']
- Pattern matching using SQL simple regular expression comparison. Returns 1 (TRUE) or 0 (FALSE). If either expr or pat is NULL, the result is NULL.
- The pattern need not be a literal string. For example, it can be specified as a string expression or table column.
- Per the SQL standard, LIKE performs matching on a per-character basis, thus it can produce results different from the = comparison operator.
- LIKE operator uses WILDCARDS (i.e. %, _) to match the pattern. This is very useful to check whether a particular character or string is present in the records.
% is used to match any number of characters, even zero characters.
_ is used to match exactly one character.
To test for literal instances of a wildcard character, precede it by the escape character. If you do not specify the ESCAPE character, “\” is assumed.
\% is used to match one "%" character.
\_ Matches one "_" character
MySQL Version: 5.6
Example: MySQL LIKE operator
The following MySQL statement scans the whole author table to find any author name which has a first name starting with character ‘W’ followed by any characters.
Code:
SELECT aut_name, country
FROM author
WHERE aut_name LIKE 'W%';
Relational Algebra Expression:

Relational Algebra Tree:

Sample table: author
Sample Output:
mysql> mysql> SELECT aut_name, country -> FROM author -> WHERE aut_name LIKE 'W%'; +-----------------+---------+ | aut_name | country | +-----------------+---------+ | William Norton | UK | | William Maugham | Canada | | William Anthony | UK | +-----------------+---------+ 3 rows in set (0.05 sec)
PHP script:
<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>example-like- php mysql examples | w3resource</title>
<meta name="description" content="example-like- php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>List of authors whose name starts with 'w', along with their country:</h2>
<table class='table table-bordered'>
<tr>
<th>Publisher</th><th>Country</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username";
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT aut_name, country
FROM author
WHERE aut_name LIKE "W%"') as $row) {
echo "<tr>";
echo "<td>" . $row['aut_name'] . "</td>";
echo "<td>" . $row['country'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>
JSP script:
<%@page contentType="text/html" pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="java.io.*" %>
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>example-like</title>
</head>
<body>
<%
try {
Class.forName("com.mysql.jdbc.Driver").newInstance();
String Host = "jdbc:mysql://localhost:3306/w3resour_bookinfo";
Connection connection = null;
Statement statement = null;
ResultSet rs = null;
connection = DriverManager.getConnection(Host, "root", "datasoft123");
statement = connection.createStatement();
String Data ="SELECT aut_name,country FROM author WHERE aut_name LIKE 'W%'";
rs = statement.executeQuery(Data);
%>
<TABLE border="1">
<tr width="10" bgcolor="#9979">
<td>Publisher</td>
<td>Country</td>
</tr>
<%
while (rs.next()) {
%>
<TR>
<TD><%=rs.getString("aut_name")%></TD>
<TD><%=rs.getString("country")%></TD>
</TR>
<% } %>
</table>
<%
rs.close();
statement.close();
connection.close();
} catch (Exception ex) {
out.println("Can’t connect to database.");
}
%>
</body>
</html>
Example: MySQL LIKE operator matching to end
The following MySQL statement scans the whole author table to find any author which have the name ended with ‘on’ string.
Code:
SELECT aut_name, country
FROM author
WHERE aut_name LIKE '%on';
Relational Algebra Expression:

Relational Algebra Tree:

Sample table: author
Sample Output:
mysql> SELECT aut_name, country -> FROM author -> WHERE aut_name LIKE '%on'; +----------------+---------+ | aut_name | country | +----------------+---------+ | William Norton | UK | | Thomas Merton | USA | | Piers Gibson | UK | | Joseph Milton | USA | +----------------+---------+ 4 rows in set (0.00 sec)
Example : MySQL LIKE operator matching within the string
The following MySQL statement scans the whole author table to find any author which have a string ‘an’ in his name. Name of the author is stored in aut_name column.
Code:
SELECT aut_name, country
FROM author
WHERE aut_name LIKE '%an%';
Relational Algebra Expression:

Relational Algebra Tree:

Sample table: author
Sample Output:
mysql> SELECT aut_name, country -> FROM author -> WHERE aut_name LIKE '%an%'; +----------------------+-----------+ | aut_name | country | +----------------------+-----------+ | William Anthony | UK | | S.B.Swaminathan | India | | Thomas Morgan | Germany | | John Betjeman Hunter | Australia | | Evan Hayek | Canada | | Butler Andre | USA | +----------------------+-----------+ 6 rows in set (0.00 sec)
Example : MySQL LIKE operator matching a specified string
The following MySQL statement searches all authors whose home city are such as ‘London’, ’Landon’ etc. the underscore wildcard is used to mention single character.
Code:
SELECT aut_name, country,home_city
FROM author
WHERE home_city LIKE 'L_n_on';
Relational Algebra Expression:

Relational Algebra Tree:

Sample table: author
Sample Output:
mysql> SELECT aut_name, country,home_city -> FROM author -> WHERE home_city LIKE 'L_n_on'; +--------------+---------+-----------+ | aut_name | country | home_city | +--------------+---------+-----------+ | Piers Gibson | UK | London | | C. J. Wilde | UK | London | +--------------+---------+-----------+ 2 rows in set (0.00 sec)
Example : MySQL LIKE operator matching escape character
To search a wildcard character or a combination of a wildcard character and any other character, the wildcard character must be preceded by an ESCAPE string. In MySQL, the default ESCAPE string is "\". The following MySQL statement returns those records, whose isbn_no contain '_16'.
code:
SELECT book_name,isbn_no,no_page,book_price
FROM book_mast
WHERE isbn_no LIKE '%\_16%';
Relational Algebra Expression:

Relational Algebra Tree:

Sample table: book_mast
Sample Output:
mysql> SELECT book_name,isbn_no,no_page,book_price -> FROM book_mast -> WHERE isbn_no LIKE '%\_16%'; +---------------------------------+-------------+---------+------------+ | book_name | isbn_no | no_page | book_price | +---------------------------------+-------------+---------+------------+ | Networks and Telecommunications | 00009790_16 | 95 | 45.00 | +---------------------------------+-------------+---------+------------+ 1 row in set (0.00 sec)
Example : MySQL LIKE operator matching beginning and ending string
Wildcards can also be used in the middle of a search pattern. The following MySQL statement will find all authors whose name begin with a ‘t’ and end with an ‘n’.
Code:
SELECT aut_name, country
FROM author
WHERE aut_name LIKE 't%n';
Relational Algebra Expression:

Relational Algebra Tree:

Sample table: author
Sample Output:
mysql> SELECT aut_name, country -> FROM author -> WHERE aut_name LIKE 't%n'; +---------------+---------+ | aut_name | country | +---------------+---------+ | Thomas Morgan | Germany | | Thomas Merton | USA | +---------------+---------+ 2 rows in set (0.00 sec)
Online Practice Editor:
Slideshow of MySQL Comparison Function and Operators
Previous: LESS THAN OPERATOR(<)
Next: NOT BETWEEN AND
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook