Filtering text

Intermediate SQL

Jasmin Ludolf

Data Science Content Developer, DataCamp

Filtering text

WHERE can also filter text

SELECT title
FROM films
WHERE country = 'Japan';

|title            |
|-----------------|
|Seven Samurai    |
|Tora! Tora! Tora!|
|Akira            |
|Madadayo         |
|Street Fighter   |
...

Filtering text

Filter a pattern rather than specific text
LIKE
NOT LIKE
IN

LIKE

Used to search for a pattern in a field

% match zero, one, or many characters

SELECT name
FROM people
WHERE name LIKE 'Ade%';

|name         |
|-------------|
|Adel Karam   |
|Adelaide Kane|
|Aden Young   |

_ match a single character

SELECT name
FROM people
WHERE name LIKE 'Ev_';

|name         |
|-------------|
|Eve          |

Ev_ Mendes

NOT LIKE

SELECT name
FROM people;

|name              |
|------------------|
|50 Cent           |
|A. Michael Baldwin|
|A. Raven Cruz     |
|A.J. Buckley      |
|A.J. DeLucia      |
...

SELECT name
FROM people
WHERE name NOT LIKE 'A.%';

|name         |
|-------------|
|50 Cent      |
|Aaliyah      |
|Aaron Ashmore|
|Aaron Hann   |
...

Wildcard position

SELECT name
FROM people
WHERE name LIKE '%r';

|name           |
|---------------|
|A.J. Langer    |
|Aaron Schneider|
|Aaron Seltzer  |
|Abigail Spencer|
...

SELECT name
FROM people
WHERE name LIKE '__t%';

|name                   |
|-----------------------|
|Aitana Sánchez-Gijón   |
|Anthony 'Critic' Campos|
|Anthony Bell           |
|Anthony Burrell        |
...

WHERE, OR

SELECT title
FROM films
WHERE release_year = 1920
OR release_year = 1930
OR release_year = 1940;

|title                         |
|------------------------------|
|Over the Hill to the Poorhouse|
|Hell's Angels                 |
|Boom Town                     |
...

WHERE, IN

SELECT title
FROM films
WHERE release_year IN (1920, 1930, 1940);

|title                         |
|------------------------------|
|Over the Hill to the Poorhouse|
|Hell's Angels                 |
|Boom Town                     |
...

WHERE, IN

SELECT title
FROM films
WHERE country IN ('Germany', 'France');

|title        |
|-------------|
|Metropolis   |
|Pandora's Box|
|The Train    |
...

Let's practice!

Intermediate SQL