LIKE, IN and BETWEEN · LIKE, IN e BETWEEN
Matching text patterns with LIKE
= 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
'S%' means "starts with S". '%a%' means "contains an a". '_o%' means "an o as the second letter".
Correspondendo padrões de texto com LIKE
= corresponde ao texto exatamente. Para corresponder a um padrão, use LIKE com dois curingas:
%representa qualquer sequência de caracteres (incluindo nenhuma)_representa exatamente um caractere
SELECT name FROM student WHERE name LIKE 'S%';
'S%' significa "começa com S". '%a%' significa "contém um a". '_o%' significa "um o como segunda letra".
Lists and ranges: IN and BETWEEN
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:
Both are shorter than writing several ORs.
Listas e intervalos: IN e BETWEEN
Mais duas verificações úteis:
IN (…)corresponde a qualquer valor em uma lista:WHERE form IN ('11A', '11C')BETWEEN low AND highcorresponde a um intervalo, incluindo ambas as extremidades:
SELECT name, score FROM student WHERE score BETWEEN 70 AND 90;
Ambos são mais curtos do que escrever vários ORs.
Common mistakes
- In
LIKE,%matches any text:'A%'means "starts with A". BETWEEN a AND bincludes both ends.
Erros comuns
- Em
LIKE,%corresponde a qualquer texto:'A%'significa "começa com A". BETWEEN a AND binclui ambas as extremidades.
Pattern & range filters · Filtros de padrão e intervalo
LIKE / IN / BETWEEN are all WHERE — they keep matching rows. · LIKE / IN / BETWEEN são todos WHERE — eles mantêm as linhas correspondentes.
Show the name of every student whose name contains the letter a. Use LIKE with %. · Exiba a name de todos os alunos cujos nomes contêm a letra a. Use LIKE com %.
Click Run to see the output here. · Clique em Executar para ver a saída aqui.
Show the name and · e score of students whose score is between 70 and 90 (inclusive). Use BETWEEN. · Exiba a name e a score dos alunos cuja nota está entre 70 e 90 (inclusive). Use BETWEEN.
Click Run to see the output here. · Clique em Executar para ver a saída aqui.