We have seen SQL SELECT command to fetch data from MySQL table. We can also use a conditional clause called WHERE clause to select required records.
A WHERE clause with equal 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 alongwith WHERE clause.
If SQL LIKE clause is used along with % characters then it will work like a meta character (*) in Unix while listing out all the files or directories at command prompt.
Without a % character LIKE clause is very similar to equal sign alongwith WHERE clause.
Syntax:
Here is generic SQL syntax of SELECT command along with LIKE clause to fetch data from MySQL table:
SELECT field1, field2,...fieldN table_name1, table_name2...
WHERE field1 LIKE condition1 [AND [OR]] filed2 = 'somevalue'
You can specify any condition using WHERE clause.
You can use LIKE clause alongwith WHERE clause.
You can use LIKE clause in place of equal sign.
When LIKE is used alongwith % sign then it will work like a meta character search.
You can specify more than one conditions using AND or OR operators
A WHERE...LIKE clause can be used alongwith DELETE or UPDATE SQL command also to specify a condition.
Using LIKE clause at Command Prompt:
This will use SQL SELECT command with WHERE...LIKE clause to fetch selected data from MySQL table tutorials_tbl
Example:
Following example will return all the records from tutorials_tbl table for which author name ends with jay:
root@host# mysql -u root -p password;
Enter password:*******
mysql> use TUTORIALS;
Database changed
mysql> SELECT * from tutorials_tbl
-> WHERE tutorial_author LIKE '%jay';
+-------------+----------------+-----------------+-----------------+
| tutorial_id | tutorial_title | tutorial_author | submission_date |
+-------------+----------------+-----------------+-----------------+
| 3 | JAVA Tutorial | Sanjay | 2007-05-21 |
+-------------+----------------+-----------------+-----------------+
1 rows in set (0.01 sec)