MySQL SELECT WHERE LIKE

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 (_).

Example1: Get all the first names that start with ‘v’

SELECT * FROM student_name_details WHERE student_fname LIKE "v%";

Output:-

Example2: Get all the first names (column student_fname) that end with ‘a’

SELECT * FROM student_name_details WHERE student_fname LIKE "%a";

Output:-

Example3: Get all the last names (column student_lname) that have ‘ia’ somewhere

SELECT * FROM student_name_details WHERE student_lname LIKE "%ia%";

Output:-

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_";

Output:-

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%";

Output:-

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%";

Output:-

We can see that the output in image_6 is the same as in image_7.

READ MORE:

• MySQL SELECT WHERE
• MySQL SELECT WHERE IN SubQuery

We hope this article helped you with SELECT WHERE LIKE statements. Good Luck !!!