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. | |
| ‘_ook’ | The book was in the car. | |
| ‘[afw]as’ | The book was in the car. | |
| ‘c[b-z]r’ | 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
