In this article, we will go through various examples of MySQL SELECT WHERE with the LIKE clause. LIKE is generally used in the SELECT statements when the user does not have the string’s full details and when the user wants similar results with a specified pattern. We will be discussing the usage of characters (%) percentage and (_) underscore.
Let us get started by making the sample data. We will be creating a table student_name_details followed by inserting some rows into it.
#create the table student_name_details CREATE TABLE student_name_details ( student_id INT, student_fname VARCHAR(255), student_lname VARCHAR(255) ); #insert rows to student_name_details INSERT INTO student_name_details(student_id,student_fname,student_lname) VALUES(1,'Daniel','Smith'), (2,'Sandy','Johnson'), (3,'Veronica','Williams'), (4,'Jennifer','Brown'), (5,'Austin','Brown'), (6,'Veronica','Jones'), (7,'George','Davis'), (8,'Veronica','Smith'), (9,'Havard','Williams'), (10,'Mounica','Miller'), (11,'Will','Smith'), (12,"Ryma",'Williams');
To have a snapshot of the data in student_name_details execute:
SELECT * FROM student_name_details;
Let us now go through a few examples to illustrate the working of LIKE with (%) and (_).
|%||% (percentage), when used with LIKE in a SELECT WHERE statement, % (percentage) will retrieve results with anyone or many characters replacing it. These can be any characters.|
|_||_ (underscore), when used with LIKE in a SELECT WHERE statement, _ (underscore) will retrieve results with only one character replacing it. It can be any character.|
Example1: Get all the first names that start with ‘v’
SELECT * FROM student_name_details WHERE student_fname LIKE "v%";
Example2: Get all the first names (column student_fname) that end with ‘a’
SELECT * FROM student_name_details WHERE student_fname LIKE "%a";
Example3: Get all the last names (column student_lname) that have ‘ia’ somewhere
SELECT * FROM student_name_details WHERE student_lname LIKE "%ia%";
Example4: Get all the last names that have character ‘w’ followed by only one character in the last
SELECT * FROM student_name_details WHERE student_lname LIKE "%w_";
Example5: Get all the last names with a single character at the start of the column student_fname followed by character ‘a’, and finally, any character(s) can trail the end
SELECT * FROM student_name_details WHERE student_fname LIKE "_a%";
Note that all the above queries will work the same way even if we change the case from lower to upper. For Example, observe the below query to retrieve the same results as in image_6.
SELECT * FROM student_name_details WHERE student_fname LIKE "_A%";
We can see that the output in image_6 is the same as in image_7.
We hope this article helped you with SELECT WHERE LIKE statements. Good Luck !!!