SQL and MySQL course Β· Module 3: Filtering (WHERE)
Underscore and NOT LIKE
In this lesson3
You already know the percent sign %. Now we will complete your knowledge with the second wildcard - the underscore _ - and with the negation of a pattern: NOT LIKE.
Underscore _ - exactly one character
The _ character replaces exactly one arbitrary character (no more, no less):
SELECT * FROM autorzy
WHERE imie LIKE 'J_n';This matches e.g. Jan, Jon - three letters, where the first is J, the last is n, and the middle one is arbitrary. It will not match Jaan (four letters) nor Jn (two letters), because each _ is exactly one character.
Percent versus underscore
| Character | Meaning |
|---|---|
% | any sequence of characters (including empty) |
_ | exactly one arbitrary character |
They can be combined, e.g. 'A_%' means: starts with A, then exactly one arbitrary character, and then anything.
NOT LIKE - what does NOT match
To find rows that do not match the pattern, we use NOT LIKE:
SELECT * FROM ksiazki
WHERE tytul NOT LIKE 'O%';This means: "Titles that do NOT start with the letter O". The NOT operator reverses the action of LIKE, just as it reversed an ordinary condition.
Thanks to %, _ and NOT LIKE you have full control over pattern searching. No scroll will hide from the eye of an experienced scribe!
Spotted a mistake in this lesson?
Check yourself
Answer the questions from this lesson. Pick an answer to see right away whether it is correct.
1. How many characters does the underscore _ replace in a LIKE pattern, e.g. 'J_n' ?
2. What will the condition return: WHERE tytul NOT LIKE 'O%' ?
Hands-on tasks in the game
- Code editor
Write a query that retrieves all columns from the autorzy table for authors whose imie matches the pattern 'J_n' (first letter J, middle one arbitrary, last n). Use the LIKE operator.
- Vertical ordering
Arrange the lines of a query searching for surnames starting with 'Kow' (from the first line to the last):