SQL – LIKE

SQL – LIKE

In order to search for a particular pattern in a column, you can use the LIKE operator in the WHERE clause.

When you use the LIKE operator you can use one of the 2 wildcards supported based on the situation.

  • % – The percent sign represents zero, one, or multiple characters
  • _ – The underscore represents a single character

SYNTAX –

SELECT column1, column2, … FROM table_name WHERE columnN LIKE pattern;



Illustration – Let’s consider the below student table stored in the database –

student_id First_Name Last_Name Color Age Grade
1000 John Miller red 15 ninth
1001 Brigitta Dillon blue 15 ninth
1002 Peter Pipper green 13 seventh
1003 Judy Windle red 12 sixth
1004 Sampson Hansy white 10 fourth

Query 1 –  Fetch the records from the student table whose first names start with letter ‘J’

                    SELECT * FROM student WHERE first_name LIKE ‘J%’;

student_id First_Name Last_Name Color Age Grade
1000 John Miller red 15 ninth
1003 Judy Windle red 12 sixth

Query 2 –  Fetch the records from the student table whose last names end with letter ‘r’

                    SELECT * FROM student WHERE last_name LIKE ‘%r’;

student_id First_Name Last_Name Color Age Grade
1000 John Miller red 15 ninth
1002 Peter Pipper green 13 seventh

Query 3 –  Fetch the records from the student table whose first names contain the letter ‘t’

                    SELECT * FROM student WHERE first_name LIKE ‘%t%’;

student_id First_Name Last_Name Color Age Grade
1001 Brigitta Dillon blue 15 ninth
1002 Peter Pipper green 13 seventh

Query 4 –  Fetch the records from the student table whose first name or last name contains the letters ‘e’

                    SELECT * FROM student WHERE first_name LIKE ‘%e%’ OR last_name LIKE ‘%e%’;

student_id First_Name Last_Name Color Age Grade
1000 John Miller red 15 ninth
1002 Peter Pipper green 13 seventh
1003 Judy Windle red 12 sixth

Query 5 –  Fetch the records from the student table whose last names contain the letter ‘i’ in second position

                    SELECT * FROM student WHERE last_name LIKE ‘_i%’;

student_id First_Name Last_Name Color Age Grade
1000 John Miller red 15 ninth
1001 Brigitta Dillon blue 15 ninth
1002 Peter Pipper green 13 seventh
1003 Judy Windle red 12 sixth

Query 6 –  Fetch the records from the student table whose colors have the letter ‘e’ followed by at least one other letter

                    SELECT * FROM student WHERE color like ‘%e_%’;

student_id First_Name Last_Name Color Age Grade
1000 John Miller red 15 ninth
1002 Peter Pipper green 13 seventh
1003 Judy Windle red 12 sixth

Notice that the colors ‘white’ and ‘blue’ end with the letter ‘e’ but do not have another letter followed after the letter ‘e’ and hence did not get pulled from the above query.

Leave A Reply

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