SQL and MySQL course Β· Module 3: Filtering (WHERE)

Underscore and NOT LIKE

2 min read Β·
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

CharacterMeaning
%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. 1. How many characters does the underscore _ replace in a LIKE pattern, e.g. 'J_n' ?

  2. 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):

  1. QuestionThe underscore wildcard
  2. QuestionThe NOT LIKE operator
  3. Coding taskNames like J_n
  4. Put in orderSurnames starting with Kow

Useful articles