Text patterns with LIKE

Module 2 of 9 SQLite in your browser

Lesson concept What you'll do

1 / 4

Find titles that start with or contain some text.

LIKE with %, which stands for any characters.

Next: Matching part of the text

On your own New data, same idea

Show the title and author of the books with Girl anywhere in the title.

The database

10 rows

book_id	title	author	genre	year	pages
1	The Fellowship of the Ring	J.R.R. Tolkien	Fantasy	1954	423
2	The Great Gatsby	F. Scott Fitzgerald	Fiction	1925	180
3	Charlotte's Web	E.B. White	Children	1952	184
4	Dracula	Bram Stoker	Horror	1897	418
5	Pride and Prejudice	Jane Austen	Romance	1813	432
6	The Girl with the Dragon Tattoo	Stieg Larsson	Thriller	2005	465
7	Angels & Demons	Dan Brown	Thriller	2000	616
8	Mockingjay	Suzanne Collins	Science Fiction	2010	390
9	Gone Girl	Gillian Flynn	Thriller	2012	419
10	The Time Machine	H.G. Wells	Science Fiction	1895	118

Your query Ctrl + Enter runs it

Hint: click the green edit button on the board, or type it yourself

Query output Ready to run

Run your query to see the result table.

Press the green Run button or Ctrl+Enter.

Study notes

How LIKE works

What does LIKE do?

WHERE title LIKE 'Harry Potter%'
It compares text with a pattern instead of exact text. Use it after WHERE, where you'd otherwise write =.
What does % mean?

Any text at all, including none. 'The%' starts with The, '%Girl' ends with Girl, '%Ring%' has "Ring" anywhere.
What does _ mean?

Exactly one character. 'Emm_' matches Emma, but not Emmanuel.
Does LIKE care about capitals?

In SQLite, not for A to Z: LIKE 'harry%' finds Harry Potter. Some databases do care; the table below shows which.
How is this like Excel?

The same wildcards as COUNTIF and XLOOKUP's wildcard mode, with * written as % and ? written as _.

Other databases

The lessons run on SQLite. Most of SQL is the same everywhere; these are the places where this lesson's SQL differs.

Does LIKE 'harry%' find Harry Potter?
Database	How	Note
SQLite (these lessons)	`Yes`	LIKE ignores capitals for A to Z (not for accented letters).
PostgreSQL	`No`	LIKE cares about capitals. ILIKE ignores them.
MySQL / MariaDB	`Yes`	The usual collations ignore capitals.
SQL Server	`Yes`	The default collation ignores capitals.
Oracle	`No`	LIKE cares about capitals. Use UPPER(title) LIKE 'HARRY%'.
Sybase ASE	`No`	The default binary sort order cares about capitals.

Extra pattern characters
Database	How	Note
SQLite (these lessons)	`% and _`	_ stands for exactly one character. GLOB uses * and ? and cares about capitals.
PostgreSQL	`% and _`	SIMILAR TO and ~ give fuller patterns (regular expressions).
MySQL / MariaDB	`% and _`	REGEXP gives fuller patterns.
SQL Server	`% _ [ ]`	[abc] matches one of a, b or c: LIKE '[A-C]%' starts with A, B or C.
Oracle	`% and _`	REGEXP_LIKE gives fuller patterns.
Sybase ASE	`% _ [ ]`	[abc] matches one of a, b or c, as in SQL Server.

Know someone fighting with a spreadsheet? Send them this page.