How to Use the LIKE clause in MySQL,

How to Use the LIKE clause in MySQL,

MySQL LIKE syntax
The LIKE operator is used in the WHERE expression to search for the specified content in a matching field. The syntax is as follows:

WHERE column LIKE patternWHERE column NOT LIKE pattern

When the NOT operator is added before the LIKE operator, it indicates the opposite of the LIKE operator, that is, select a data record that does NOT contain pattern in the column.
LIKE is usually used with the wildcard %, and % indicates content not displayed in the wildcard pattern. Without the LIKE Syntax of wildcard %, it indicates exact match. The actual effect is equivalent to the = equals operator.
LIKE instance
The following is an example of using LIKE to query data:
Raw user table data:

SELECT * FROM user WHERE username LIKE 'small %'

The returned query result is as follows:

In this example, all records whose usernames start with "small" are found. The "small" character indicates that the usernames start with "small" and can be followed by any character. Similarly, % indicates to end with "small, % small % indicates that it contains the character "small" (and includes both '% small' and 'small % ).
In the following example, all records whose fields in username contain the character at any position are queried:

SELECT * FROM user WHERE username LIKE '%a%'

MySQL LIKE case
MySQL LIKE is case-insensitive by default when matching characters. If you need to be case-sensitive during matching, you can add the BINARY operator:

SELECT * FROM user WHERE username LIKE BINARY '%azz%'SELECT * FROM user WHERE username LIKE BINARY '%aZZ%'

The BINARY operator is used to compare data by BINARY. Therefore, after the operator is added, it can be case sensitive. Therefore, the content of the preceding two SQL statements is different.
MySQL LIKE Chinese Character matching
Due to the data storage encoding problem, in some cases, in addition to the data that meets the requirements in the LIKE search returned by MySQL, many irrelevant data are often returned. At this time, you also need to add the BINARY operator after LIKE for BINARY comparison:

SELECT * FROM user WHERE username like binary '%'

Prompt
After adding the BINARY operator to LIKE matching, it is strictly case sensitive. Therefore, when the retrieved content is a mix of Chinese and English characters and the English case must be ignored, you may encounter problems. To solve this problem, we need to introduce the UPPER () and CONCAT () functions in MySQL:
UPPER (): converts an English string to uppercase, which is the same as UCASE ()
CONCAT (): concatenates multiple strings into one string.
Syntax:

UPPER(str)CONCAT(str1,str2,...)

Therefore, you can use the SQL statement shown in the following example to perform a hybrid English-Chinese search and ignore the English case:
SELECT * FROM username where upper (username) like binary CONCATt ('%', UPPER ('a Chinese B '),' % ')
In this SQL statement, the fields searched and the retrieved content are converted in uppercase before binary matching.
LIKE operator Efficiency
The LIKE operator performs scanning and matching on the field data one by one. The actual execution efficiency is poor, even if the field has an index (a % will be used in this way ). When the data volume is large, we need to minimize the use of the LIKE operator, and there is no room for optimization.
Use the LIKE clause in a PHP script
You can use a syntax similar to the WHERE... LIKE clause in the mysql_query () function of PHP. This function is used to execute SQL commands, followed by another PHP mysql_fetch_array () function that can be used to obtain all selected data, if the WHERE... LIKE clause is used together with the SELECT command.

However, if the WHERE... LIKE clause is being used together with the DELETE or UPDATE command, PHP functions are no longer required.

Example
In the following example, all records in the tutorials_tbl table whose author name contains jay will be returned:

<?php$dbhost = 'localhost';$dbuser = 'root';$dbpass = '';$conn = mysql_connect($dbhost, $dbuser, $dbpass);if(! $conn ){ die('Could not connect: ' . mysql_error());}$sql = 'SELECT tutorial_id, tutorial_title,         tutorial_author, submission_date    FROM tutorials_tbl    WHERE tutorial_author LIKE "%jay%"';mysql_select_db('test');$retval = mysql_query( $sql, $conn );if(! $retval ){ die('Could not get data: ' . mysql_error());}while($row = mysql_fetch_array($retval, MYSQL_ASSOC)){  echo "Tutorial ID :{$row['tutorial_id']} <br> ".     "Title: {$row['tutorial_title']} <br> ".     "Author: {$row['tutorial_author']} <br> ".     "Submission Date : {$row['submission_date']} <br> ".     "--------------------------------<br>";} echo "Fetched data successfully\n";mysql_close($conn);?>

Articles you may be interested in:

• Usage of MySQL Like statements
• MySql like fuzzy query wildcard usage
• Mysql like query string Example Statement
• Mysql regular expression LIKE wildcard