AI Hints
Homeโ€บSQL Tutorialโ€บFiltering Rows
SQL ยท Lesson 8 of 126

Filtering on Text

Quotes and capitals.

โฑ 2 min readโ–ถ Video at 11:00๐ŸŽ“ Free
Watch this lesson in the SQL courseWatch on YouTube โ†—Full SQL course

A customer emails Helen. I love mysteries, what do you have? To answer, we need to find rows where the genre is the word Mystery. Text works almost the same as numbers, with two small differences.

Filtering on Text
From the video: Filtering on Text
  • A customer loves mysteries
  • Find genre = Mystery
  • Two small differences

Text needs quotes

The first difference is quotes. Text values always go inside single quotes, like this. Without the quotes, SQL would think Mystery was the name of a column. Run it. Four mystery books, three of them by Mira Hale.

query.sqlsql
SELECT title, author, price
FROM books
WHERE genre = 'Mystery';
Output
โ”Œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ฌโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”
โ”‚        title        โ”‚   author   โ”‚ price โ”‚
โ”œโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ผโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ค
โ”‚ The Silent Orchard  โ”‚ Mira Hale  โ”‚ 14.99 โ”‚
โ”‚ The Last Lighthouse โ”‚ Mira Hale  โ”‚ 13.99 โ”‚
โ”‚ Murder at Mill Lane โ”‚ Mira Hale  โ”‚ 10.99 โ”‚
โ”‚ The Painted Door    โ”‚ Nora Wells โ”‚ 12.49 โ”‚
โ””โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”ดโ”€โ”€โ”€โ”€โ”€โ”€โ”€โ”˜

Capitals matter here

The second difference is capital letters. Here we typed mystery with a small m. Run it. Nothing comes back! Inside quotes, Mystery with a capital M, and mystery with a small m, are different text. Keywords like select don't care about capitals, but the data you search for does.

query.sqlsql
SELECT title
FROM books
WHERE genre = 'mystery';

Numbers versus text

Here's the difference side by side. Numbers are written as they are, with no quotes. Text goes in single quotes, and must match exactly, including capitals. If you ever get an empty result that you didn't expect, check your quotes and your capitals first.

Numbers

  • price < 10
  • No quotes
  • 10 is just 10

Text

  • genre = 'Mystery'
  • Single quotes
  • Capitals must match

Summary

Text goes in single quotes, and the capitals must match. Now let's meet all the ways you can compare things.

Key takeaways
  • 'Text' in single quotes
  • Capitals must match
  • Empty result? Check quotes
Next up: Comparing Values โ€” equal, not equal, more, less