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;
image_1: student_name_details

Let us now go through a few examples to illustrate the working of LIKE with (%) and (_).

CharacterDescription
%% (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.
table_1

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

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

Output:-

image_2

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

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

Output:-

image_3

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

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

Output:-

image_4

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:-

image_5

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:-

image_6

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:-

image_7

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

READ MORE:

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

You need to add a widget, row, or prebuilt layout before you’ll see anything here. 🙂

Leave a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Scroll to Top