LIKE, IN and BETWEEN

= only matches text exactly. To match a pattern, use LIKE with two wildcards:

• % stands for any run of characters (including none)
• _ stands for exactly one character

SELECT name FROM student WHERE name LIKE 'S%';

'S%' means "starts with S". '%a%' means "contains an a". '_o%' means "an o as the second letter".

Two more handy tests:

• IN (…) matches any value in a list: WHERE form IN ('11A', '11C')
• BETWEEN low AND high matches a range, including both ends:

SELECT name, score FROM student WHERE score BETWEEN 70 AND 90;

Both are shorter than writing several ORs.

Question. Show the name of every student whose name has a as its second letter.

Answer. _ stands for exactly one character, so _a means "any first letter, then a". The % lets the rest of the name be anything:

SELECT name FROM student WHERE name LIKE '_a%';

name
Jamal
Sara

'%a%' would be the wrong pattern here: it means "an a anywhere", and would also match a name such as Ana.

• BETWEEN wants the smaller value first. BETWEEN 90 AND 70 matches nothing; write BETWEEN 70 AND 90.
• % and _ only work with LIKE. WHERE name = 'S%' looks for a name that is literally S%.
• Case. In SQLite, LIKE ignores the difference between capital and small letters A to Z, so 's%' also finds Sara. Some other databases do not.