LIKE and NOT LIKE

An introduction to the LIKE and NOT LIKE keywords.

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:

monthyear name age_upon_outcome sex_upon_outcome outcome_type outcome_subtype breed
2013-12 *Seamus 2 years Neutered Male Transfer Partner Irish Terrier/Irish Wolfhound
2014-01 Elsa 7 years Spayed Female Adoption Irish Wolfhound Mix
2014-02 *Nick 3 years Neutered Male Transfer Partner Irish Wolfhound/Great Pyrenees
2014-05 Sherlock 3 years Neutered Male Adoption Irish Wolfhound Mix
2014-06 4 months Neutered Male Adoption Catahoula/Irish Wolfhound
2015-11 Sadie 6 years Intact Female Return to Owner Irish Wolfhound/Great Dane
2015-12 Buddy 10 months Neutered Male Transfer Partner Irish Wolfhound/Australian Shepherd
2016-01 Dollar 4 years Intact Male Return to Owner Irish Wolfhound Mix
2016-01 Dollar 4 years Neutered Male Return to Owner Irish Wolfhound Mix
2016-12 Molly 6 years Intact Female Transfer Partner Irish Wolfhound Mix
2017-04 Aurora 1 year Spayed Female Adoption Irish Wolfhound Mix
2017-04 Aurora 1 year Spayed Female Adoption Irish Wolfhound Mix

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:

monthyear age_upon_outcome sex_upon_outcome outcome_type outcome_subtype breed color
2013-10 0 years Intact Male Transfer Partner Domestic Shorthair Mix Cream
2013-10 0 years Unknown Transfer Partner Domestic Shorthair Mix Gray
2013-10 1 day Intact Male Died In Foster Domestic Shorthair Mix Black
2013-10 1 day Intact Male Died In Foster Domestic Shorthair Mix Black/White
2013-10 1 day Intact Female Transfer Partner Domestic Shorthair Mix Tortie
2013-10 1 month Spayed Female Adoption Snowshoe Mix Calico Point/White
2013-10 1 month Neutered Male Adoption Domestic Shorthair Mix Chocolate/Black Smoke
2013-10 1 month Intact Male Adoption Domestic Shorthair Mix Orange Tabby
2013-10 1 month Intact Female Adoption Domestic Shorthair Mix Torbie
2013-10 1 month Intact Male Died In Kennel Domestic Shorthair Mix Blue/White