Match text with patterns
Find rows by part of a text with LIKE, and join or reshape text with functions. About 8 minutes.
LIKE finds text by shape
Equals needs the whole value. When you only know part of it, a book whose title starts with "The", a city ending in "pur", use LIKE with a pattern.
Two characters are special. The percent sign stands for any run of characters, even none. The underscore stands for exactly one character. Everything else must match itself.
city LIKE 'S%' -- starts with S city LIKE '%pur' -- ends with pur city LIKE '%an%' -- contains an code LIKE 'B__' -- B and two more
| id | city |
|---|---|
| 1 | Jaipur |
| 2 | Pune |
| 3 | Udaipur |
| 4 | Mumbai |
| 5 | Kanpur |
Jaipur ends in pur, and % covers the Jai. Kept.
Reshape text with functions
Functions work on the text in a column. lower() and upper() change the case, and length() counts the characters. Two bars, ||, join pieces together.
One thing to watch: in SQLite, LIKE ignores capital letters for English letters, so 'an' matches 'Anan'. Postgres is case-sensitive unless you ask otherwise, and MySQL depends on its settings. Also, joining anything with NULL gives NULL.
SELECT upper(name) || ' (' || city || ')'
FROM members;Which value does name LIKE 'R_n' match?
- ARon
- BRohan
- CRn
Show the answer
Ron. R, then exactly one character, then n.
Names with an
The students table has one row per student. Show the name of every student whose name contains the letters an anywhere, in any capitalisation, A to Z.
students(id, name, branch)
Up to 2,000 students