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 highmatches 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.
BETWEENwants the smaller value first.BETWEEN 90 AND 70matches nothing; writeBETWEEN 70 AND 90.%and_only work withLIKE.WHERE name = 'S%'looks for a name that is literallyS%.- Case. In SQLite,
LIKEignores the difference between capital and small letters A to Z, so's%'also finds Sara. Some other databases do not.
Pattern & range filters
LIKE / IN / BETWEEN are all WHERE — they keep matching rows.
Сложность 2/5 · 140 монет + небольшой бонус за первое завершение сегодня
Show the name of every student whose name contains the letter a. Use LIKE with %.
Нажмите Запустить, чтобы увидеть результат здесь.
Сложность 2/5 · 140 монет + небольшой бонус за первое завершение сегодня
Show the name и score of students whose score is between 70 and 90 (inclusive). Use BETWEEN.
Нажмите Запустить, чтобы увидеть результат здесь.
Сложность 2/5 · 140 монет + небольшой бонус за первое завершение сегодня
Show the name и form of every student in form 11B или 11C. Use IN with a list of the two forms.
Нажмите Запустить, чтобы увидеть результат здесь.
Сложность 2/5 · 140 монет + небольшой бонус за первое завершение сегодня
Show the name of every student whose name is exactly three letters long. Each _ stands for one character.
Нажмите Запустить, чтобы увидеть результат здесь.