\\n

We know that in MySQL, we use the SELECT command to read data. At the same time, we can use the WHERE clause in the SELECT statement to fetch specific records.

\\n

The WHERE clause can use the equals sign = to set the condition for fetching data, such as "tutorial_author = '.COM'".

\\n

However, sometimes we need to fetch all records where the tutorial_author field contains the string "COM". In this case, we need to use the LIKE clause in the WHERE clause.

\\n

The LIKE clause is a keyword in MySQL used for fuzzy matching within the WHERE clause. It is typically used with wildcards to search for strings that match a certain pattern.

\\n

In the LIKE clause, the percent sign % character is used to represent any number of characters, similar to the asterisk * in UNIX or regular expressions.

\\n

If the percent sign % is not used, the LIKE clause behaves the same as the equals sign =.

\\n\\n

Syntax

\\n

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

\\n

\\nSELECT column1, column2, ...\\nFROM table_name\\nWHERE column_name LIKE pattern;\\n    

\\n

Parameter Description:

\\n

\\n
• column1, column2, ... are the names of the columns you want to select. Use * to select all columns.
\\n
• table_name is the name of the table from which you want to query data.
\\n
• column_name is the name of the column to which you want to apply the LIKE clause.
\\n
• pattern is the pattern used for matching, which can contain wildcards.
\\n

\\n

More Notes:

\\n

\\n
• You can specify any condition in the WHERE clause.
\\n
• You can use the LIKE clause in the WHERE clause.
\\n
• You can use the LIKE clause instead of the equals sign =.
\\n
• LIKE is often used with %, similar to a metacharacter search.
\\n
• You can use AND or OR to specify one or more conditions.
\\n
• You can use the WHERE...LIKE clause in DELETE or UPDATE commands to specify conditions.
\\n

\\n\\n

Examples

\\n

Here are some examples of using the LIKE clause.

\\n

1. Percent Wildcard %:

\\n

The % wildcard represents zero or more characters. For example, 'a%' matches any string that starts with the letter 'a'.

\\n

\\nSELECT * FROM customers WHERE last_name LIKE 'S%';\\n    

\\n

The above SQL statement will select all customers whose last name starts with 'S'.

\\n

2. Underscore Wildcard _:

\\n

The _ wildcard represents a single character. For example, '_r%' matches any string where the second letter is 'r'.

\\n

\\nSELECT * FROM products WHERE product_name LIKE '_a%';\\n    

\\n

The above SQL statement will select all products where the second character of the product name is 'a'.

\\n

3. Combining % and _:

\\n

\\nSELECT * FROM users WHERE username LIKE 'a%o_';\\n    

\\n

The above SQL statement will match strings that start with the letter 'a', followed by zero or more characters, then 'o', and finally any single character, such as 'aaron' or 'apol'.

\\n

4. Case-Insensitive Matching:

\\n

\\nSELECT * FROM employees WHERE last_name LIKE 'smi%' COLLATE utf8mb4_general_ci;\\n    

\\n

The above SQL statement will select all employees whose last name starts with 'smi', regardless of case.

\\n

The LIKE clause provides powerful fuzzy search capabilities and can be customized based on different patterns and requirements. When using it, ensure you understand the meaning of the wildcards and match according to the actual situation.

\\n\\n

Using the LIKE Clause in the Command Prompt

\\n

Below, we will use the WHERE...LIKE clause in a SELECT command to read data from the MySQL table tutorial_tbl.

\\n

Example

\\n

Here, we will fetch all records from the tutorial_tbl table where the tutorial_author field ends with 'COM'.

\\n

SQL LIKE Statement:

\\n

\\nmysql> use ;\\nDatabase changed\\nmysql> SELECT * from tutorial_tbl  WHERE tutorial_author LIKE '%COM';\\n+-----------+---------------+---------------+-----------------+\\n| tutorial_id | tutorial_title  | tutorial_author | submission_date |\\n+-----------+---------------+---------------+-----------------+\\n| 3         | Learn Java    | .COM    | 2015-05-01      |\\n| 4         | Learn Python  | .COM    | 2016-03-06      |\\n+-----------+---------------+---------------+-----------------+\\n2 rows in set (0.01 sec)\\n    

\\n\\n

Using the LIKE Clause in a PHP Script

\\n

You can use the PHP function mysqli_query() with the same SELECT command including the WHERE...LIKE clause to fetch data.

\\n

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

\\n

However, if the WHERE...LIKE clause is used in a DELETE or UPDATE SQL statement, the mysqli_fetch_array() function is not needed.

\\n

Example

\\n

Here, we use a PHP script to read all records from the tutorial_tbl table where the tutorial_author field ends with 'COM'.

\\n

MySQL LIKE Clause Test:

\\n

\\n<?php\\n$dbhost = 'localhost';  // MySQL server host address\\n$dbuser = 'root';            // MySQL username\\n$dbpass = '123456';          // MySQL user password\\n$conn = mysqli_connect($dbhost, $dbuser, $dbpass);\\nif(! $conn )\\n{\\n    die('Connection failed: ' . mysqli_error($conn));\\n}\\n// Set encoding to prevent 1. Red silk garbled text\\nmysqli_query($conn , "set names utf8");\\n \\n$sql = 'SELECT tutorial_id, tutorial_title, \\n        tutorial_author, submission_date\\n        FROM tutorial_tbl\\n        WHERE tutorial_author LIKE "%COM"';\\n \\nmysqli_select_db( $conn, '' );\\n$retval = mysqli_query( $conn, $sql );\\nif(! $retval )\\n{\\n    die('Failed to read data: ' . mysqli_error($conn));\\n}\\necho '<h2> mysqli_fetch_array Test<h2>';\\necho '<table border="1"><tr><td>Tutorial ID</td><td>Title</td><td>Author</td><td>Submission Date</td></tr>';\\nwhile($row = mysqli_fetch_array($retval, MYSQLI_ASSOC))\\n{\\n    echo "<tr><td> {$row['tutorial_id']}</td> ".\\n         "<td>{$row['tutorial_title']} </td> ".\\n         "<td>{$row['tutorial_author']} </td> ".\\n         "<td>{$row['submission_date']} </td> ".\\n         "</tr>";\\n}\\necho '</table>';\\nmysqli_close($conn);\\n?>\\n    

\\n

The output result is shown in the image below:

\\n

\\n

\\nSELECT * FROM position WHERE name LIKE 'java%';\\n        

\\nSELECT * FROM position WHERE name LIKE '%java%';\\n        

\\nSELECT * FROM position WHERE name LIKE '%java';\\n