LIKE and NOT LIKE

Data is often messy and inconsistent, and yet you’d like to be able to find and use it even if values that mean the same thing are written slightly differently and so don’t match exactly from row to row in a table. Similarly there are often times you’d like to exclude data from a query but you want to exclude several different ways it could have been written in the original table. The LIKE and NOT LIKE expressions allow you to specify a set of similar values to be either returned or excluded from a query’s results.

LIKE can take two wildcards, % and _ which indicate either all characters or a single character. The wildcard % is used to take the place of all possible strings that occur in its position in the search string. E.g., % before a search term means return all entries that end with the search term regardless of what comes before it. Likewise, % following the search term means return all entires that begin with the search term. The wildcard to indicate the presence of a single character is the underscore _ which can be used either singly or in multiples to indicate a set number of characters. LIKE and NOT LIKE searches are case-insensitive.

If we wanted to see the outcome data for all the wolfhounds and wolfhound mix dogs that passed through the animal center we could write the query like this:

SELECT monthyear,
       name,
       age_upon_outcome,
       sex_upon_outcome,
       outcome_type,
       outcome_subtype,
       breed
  FROM austin_animal_center_outcomes
 WHERE animal_type = "Dog"
       AND breed LIKE "%wolfhound%"
 ORDER BY monthyear

The results of the query would be:

Sometimes it’s necessary to search for an instance of one of the characters used as wildcards. It’s common to have yet another character called an escape character which is placed before a wildcard character in a search string to indicate that the character is to be taken literally and not used as a wildcard. LIKE is unusual in that any character can be declared an escape in a LIKE clause. For example if we wanted to find all the breed names that had an underscore in them the LIKE clause and we wanted to use the exclamation point (!) as our escape character, our query would look like this:

breed LIKE "%!_%" ESCAPE "!"

NOT LIKE is used the same way LIKE is, it just excludes data instead of including it. For example if we wanted to see all the cats that came through the center that were not brown we could write a query like this:

SELECT monthyear,
       age_upon_outcome,
       sex_upon_outcome,
       outcome_type,
       outcome_subtype,
       breed,
       color
  FROM austin_animal_center_outcomes
 WHERE animal_type = "Cat"
       AND color NOT LIKE "%brown%"
 ORDER BY monthyear
 LIMIT 10

The data returned from the query would look like this:

Exercises (Continued from previous section)

Exercise 15

Write a query against the medications table that returns all of the patients who were given some form of Acetaminophen, what they were given, and the reason they were given it.

Try query

Exercise 16

Use the query you just wrote to return the same columns but leave out the results of Acetaminophen with either hydrocodone or oxycodone in them. Sort your results by dose, highest to lowest.

Try query