Filtering text

Intermediate SQL with AI

Jasmin Ludolf

Senior Data Science Content Developer

Pattern matching

One book

Pattern matching

Collection of books

Wildcards

*: select all

Two pattern matching wildcards:

Wildcards

Matching text with %

Prompt: Show film titles containing 'love'

SELECT title
FROM films
WHERE title LIKE '%love%';

|title                   |
|------------------------|
|Dr. Strangelove or: H...|
|Beloved                 |
|Cloverfield             |
|The Oogieloves in the...|
...

Matches any character
Case-sensitive
%love%:
- Anything before or after "love"
- Exact lowercase "love"

Case sensitivity

Prompt: Show film titles containing 'love' in any case

SELECT title 
FROM films
WHERE title ILIKE '%love%';

|title                |
|---------------------|
|Intolerance: Love'...|
|Love Me Tender       |
|From Russia with Love|
...

Case sensitivity

SELECT title 
FROM films
WHERE LOWER(title) LIKE '%love%';

Original title: "Love Actually"
LOWER(title): "love actually"
- Match to "love"

SELECT title 
FROM films
WHERE UPPER(title) LIKE '%LOVE%';

Original title: "Love Actually"
UPPER(title): "LOVE ACTUALLY"
- Match to "LOVE"

Matching text with %

Prompt: Show film titles ending with 'and', any case

SELECT title
FROM films
WHERE LOWER(title) LIKE '%and';

|title                   |
|------------------------|
|Alexander's Ragtime Band|
|Wonderland              |
|Finding Neverland       |
...

Prompt: Show film titles starting with 'and', any case

SELECT title
FROM films
WHERE LOWER(title) LIKE 'and%';

|title             |
|------------------|
|And Then Came Love|
|Anderson's Cross  |
|And So It Goes    |

Matching text with _

Prompt: Show five-letter film titles starting with E

SELECT title
FROM films
WHERE title LIKE 'E____';

|title|
|-----|
|Evita|
|Earth|

"Elf" or "Enchanted" do not match

Excluding text values

Prompt: Show film titles whose titles don't start with 'The'

SELECT title
FROM films
WHERE title NOT LIKE 'The%';

|title               |
|--------------------|
|Intolerance: Love...|
|Over the Hill to ...|
|Metropolis          |
...

Pattern matching

Find similarities in text

Marketing emails

Pattern matching

Find similarities in text

Finance and products

Pattern matching

Find similarities in text

Case-sensitive
Wildcards
- *: selects all fields
- _: character matching
- %: position

AI with magnifying glass

LIKE: pattern matching
NOT LIKE: excludes patterns
ILIKE: case-insensitive
UPPER() / LOWER(): standardize capitalization

Let's practice!

Intermediate SQL with AI