MySQL – LIKE Clause

MySQL – LIKE Clause

We have seen the SQL SELECT command to fetch data from the MySQL table. We can also use a conditional clause called as the WHERE clause to select the required records.
A WHERE clause with the ‘equal to’ sign (=) works fine where we want to do an exact match. Like if "tutorial_author = 'Sanjay'". But there may be a requirement where we want to filter out all the results where tutorial_author name should contain "jay". This can be handled using SQL LIKE Clause along with the WHERE clause.
If the SQL LIKE clause is used along with the % character, then it will work like a meta character (*) as in UNIX, while listing out all the files or directories at the command prompt. Without a % character, the LIKE clause is very same as the equal to sign along with the WHERE clause.

Syntax

The following code block has a generic SQL syntax of the SELECT command along with the LIKE clause to fetch data from a MySQL table.

SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
  • You can specify any condition using the WHERE clause.
  • You can use the LIKE clause along with the WHERE clause.
  • You can use the LIKE clause 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 be used along with DELETE or UPDATE SQL command also to specify a condition.

    Using the LIKE clause at the Command Prompt

    This will use the SQL SELECT command with the WHERE...LIKE clause to fetch the selected data from the MySQL table – tutorials_tbl.

    Example

    The following example will return all the records from the tutorials_tbl table for which the author name ends with jay −

    root@host# mysql -u root -p password;
    Enter password:
    mysql> select * from employees where first_name LIKE '%a%';
    +----+-------------------+------------+-----------+----------------------+---------------+-----------------------------+
    | id | company           | first_name | last_name | job_title            | mobile_phone  | email_address               |
    +----+-------------------+------------+-----------+----------------------+---------------+-----------------------------+
    |  1 | Northwind Traders | Nancy      | kotas     | developer            | (123)555-0100 | nancy@northwindtraders.com  |
    |  3 | Northwind Traders | Mariya     | Jan       | Sales Representative | 444 333 8888  | mariya@northwindtraders.com |
    +----+-------------------+------------+-----------+----------------------+---------------+-----------------------------+
    2 rows in set (0.00 sec)
    mysql>

    Using LIKE clause inside PHP Script

    You can use similar syntax of the WHERE...LIKE clause into the PHP function – mysqli_query(). This function is used to execute the SQL command and later another PHP function – mysql_fetch_array() can be used to fetch all the selected data, if the WHERE...LIKE clause is used along with the SELECT command.
    But if the WHERE...LIKE clause is being used with the DELETE or UPDATE command, then no further PHP function call is required.

    Example

    Try out the following example to return all the records from the tutorials_tbl table for which the author name contains jay −

    <title>Like clause in mysql</title>
    <?php
    $servername = "localhost";
    $username = "root";
    $password = "";
    $dbname = "mydb";
    // Create connection
    $conn = mysqli_connect($servername, $username, $password, $dbname);
    // Check connection
    if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
    }
    $sql = "select * from employees where first_name LIKE '%a%'";
    $result = mysqli_query($conn, $sql);
    echo "select * from employees where first_name LIKE '%a%' <br> <hr>";
    if (mysqli_num_rows($result) > 0) {
    while($row = mysqli_fetch_array($result)) {
    echo "Sr. no:{$row['id']}  <br> ".
    "Company name:{$row['company']}  <br> ".
    "First Name: {$row['first_name']} <br> ".
    "Last Name: {$row['last_name']} <br> ".
    "Job Title: {$row['job_title']} <br> ".
    "Mobile Number: {$row['mobile_phone']} <br> ".
    "Email : {$row['email_address']} <br> ".
    "--------------------------------<br>";
    } }
    else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
    }
    ?>
MySQL – DELETE Query (Prev Lesson)
(Next Lesson) MySQL – Sorting Results
', { 'anonymize_ip': true });