MySQL LIKE Clause

We know that in MySQL, we use the SQL SELECT command to read data, and we can use the WHERE clause in the SELECT statement to retrieve specific records.

The WHERE clause can use the equal sign = to set the condition for retrieving data, such as "tutorialpro_author = 'tutorialpro.org'".

However, sometimes we need to retrieve all records where the tutorialpro_author field contains the "COM" character. In this case, we need to use the SQL LIKE clause in the WHERE clause.

The SQL LIKE clause uses the percent sign % to represent any character, similar to the asterisk * in UNIX or regular expressions.

If the percent sign % is not used, the LIKE clause has the same effect as the equal sign =.

Syntax

The following is the general syntax for using the LIKE clause in an SQL SELECT statement to read data from a database table:

SELECT field1, field2,...fieldN 
FROM table_name
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'

• You can specify any condition in the WHERE clause.
• You can use the LIKE clause in the WHERE clause.
• You can use the LIKE clause instead of the equal sign =.
• LIKE is usually used with %, similar to a wildcard search.
• You can use AND or OR to specify one or multiple conditions.
• You can use the WHERE...LIKE clause in DELETE or UPDATE commands to specify conditions.

Using the LIKE Clause in Command Prompt

Below, we will use the WHERE...LIKE clause in the SQL SELECT command to retrieve data from the MySQL table tutorialpro_tbl.

Example

The following example retrieves all records from the tutorialpro_tbl table where the tutorialpro_author field ends with COM:

SQL LIKE Statement:

mysql> use tutorialpro;
Database changed
mysql> SELECT * from tutorialpro_tbl WHERE tutorialpro_author LIKE '%COM';
+-----------+---------------+---------------+-----------------+
| tutorialpro_id | tutorialpro_title  | tutorialpro_author | submission_date |
+-----------+---------------+---------------+-----------------+
| 3         | Learn Java   | tutorialpro.org    | 2015-05-01      |
| 4         | Learn Python | tutorialpro.org    | 2016-03-06      |
+-----------+---------------+---------------+-----------------+
2 rows in set (0.01 sec)

Using the LIKE Clause in PHP Scripts

You can use the PHP function mysqli_query() with the same SQL SELECT command containing the WHERE...LIKE clause to retrieve data.

This function is used to execute the SQL command, and then all queried data is output using the PHP function mysqli_fetch_array().

However, if you use the WHERE...LIKE clause in a DELETE or UPDATE SQL statement, you do not need to use the mysqli_fetch_array() function.

Example

The following example uses a PHP script to retrieve all records from the tutorialpro_tbl table where the tutorialpro_author field ends with COM:

MySQL LIKE Clause Test:

<?php
$dbhost = 'localhost';  // mysql server hostname
$dbuser = 'root';       // mysql username
$dbpass = '123456';     // mysql username password
$conn = mysqli_connect($dbhost, $dbuser, $dbpass);
if(! $conn )
{
    die('Connection failed: ' . mysqli_error($conn));
}
// Set encoding to prevent Chinese乱码
mysqli_query($conn , "set names utf8");

$sql = 'SELECT tutorialpro_id, tutorialpro_title, 
        tutorialpro_author, submission_date
        FROM tutorialpro_tbl
        WHERE tutorialpro_author LIKE "%COM"';

mysqli_select_db( $conn, 'tutorialpro' );
$retval = mysqli_query( $conn, $sql );
if(! $retval )
{
    die('Could not read data: ' . mysqli_error($conn));
}
echo '<h2>tutorialpro.org mysqli_fetch_array Test<h2>';
echo '<table border="1"><tr><td>Tutorial ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>';
while($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))
{
    echo "<tr><td> {$row['tutorialpro_id']}</td> ".
         "<td>{$row['tutorialpro_title']} </td> ".
         "<td>{$row['tutorialpro_author']} </td> ".
         "<td>{$row['submission_date']} </td> ".
         "</tr>";
}
echo '</table>';
mysqli_close($conn);
?>

The output is shown in the following image: