SQL Like

SQL Like

This tutorial covers SQL Like operator.

The “like” operator is part of the logical operators.

This operator works well with the “where” statement. Its function is to retrieve data that matches specific character string patterns.

We also cover wildcards within this tutorial.

For more information on operators here.

Here we use two tables. First is the table “student_info” from the previous tutorials, and the second is a table named “grade“, linked to the first table with a foreign key.

id	firstName	lastName	age
1	John	Smith	21
2	Patrick	Casey	26
3	Sam	Adams	19
4	Mike	Garcia	NULL
5	Patrick	Dumont	21

id	subjectName	grade	passed	studentID
1	History	C	1	1
2	Physics	A	1	2
3	History	F	0	3
4	Biology	A	1	NULL
5	Biology	E	1	5

Wildcards

The term wildcard refers to the substitution of string characters.

They are used in search patterns, with the operator “like“.

We will cover some of the main and most popular wildcards.

More on wildcards here.

Symbol	Description	Scenario
%	Any string of characters	‘%car%’ any record containing the word “car”.
_	Any single character	‘_ook’ any record containing any word ending in “ook”.
[ ]	Any character within a range or set	‘[afw]as’ or ‘[a-z]as’ any record containing any word ending in “as”. Must have the character in front either “a/f/w” or any character between “a” and “z”.
[^]	Any character not within a range or set	‘c[^y]r’ or ‘c[b-z]r’ any record containing any word starting with “c” and ending with “r”. Must not have the character in the middle either “y” or between “a” and “z”.

Wildcard	Example	Outcome
‘%car%’	The book was in the car.	The book was in the car.
‘_ook’	The book was in the car.	The book was in the car.
‘[afw]as’	The book was in the car.	The book was in the car.
‘c[b-z]r’	The book was in the car.	The book was in the car.

This is more clear when executed with the “like” logical operator.

Like

This operator searches for specific string patterns.

It works with the “where” statement and has various wildcard options.

Let’s dive into several examples. We use the “student_info” table.

SELECT firstName FROM student_info
WHERE firstName LIKE '%rick%';

firstName
Patrick
Patrick

The above statement retrieves all first names that have the word “rick” anywhere in the record cell. The operator looks for the word “rick”, which can have any characters on the left of the word (the first % symbol) and any characters on the right (second % symbol).

SELECT firstName FROM student_info
WHERE firstName LIKE '_ike';

firstName
Mike

The above query selects all first names that have the word “ike” in any table cell. The operator looks for the word “ike”, which have any single character in front (the _ symbol).

SELECT firstName FROM student_info
WHERE firstName LIKE 's[trka]m';

firstName
Sam

The above statement selects all first names that have a word starting with “s” and ending with “m”. The middle character must have any of the letters: “t”, “r”, “k”, or “a”.

SELECT firstName FROM student_info
WHERE firstName LIKE 'Jo[^d-g]n';

firstName
John

The above query retrieves all first names that have a word starting with “Jo” and ending with “n”. The middle character must not have any of the letters between “d” and “g” (alphabet order: “d”, “e”, “f”, or “g”).

---

Next: SQL Order By