LIKE  Let‘s say we‘re looking for a cat, but couldn’t quite remember its name; however, we know it started with Gar. We can use the LIKE operator to help us find the list of names that start with Gar. We should only use the LIKE operator with text data types.  There are two components to using the LIKE operator. The first is using the actual word LIKE in the WHERE clause. The second is the use of the wildcard character % inside our character string. The % symbol tells the database we will accept anything that matches the text we have entered. In this case, the query would look like this

SELECT NameOfCat, BreedOfCat, Cat WHERE   NameOfCat LIKE 'Gar%';

OUTPUT

NameOfCat        BreedOfCat
—————-       ———————-

Garfield                Tabby
Gareth                  Snowshoe
Gary                      Sokoke
Garth                    Somali
Garret                  Turkish Angora
Garth                   Russian Tabby
Gary                     American Curl
Gareth                 American Shorthair
Garret                  Brazilian Shorthair

(9 row(s) affected)

In this query, we could get back Cat names like Garfield, Gary, Garth, Gareth, Garret, and Gargamel. With the keyword LIKE, we use part of a phrase to compare against the possible values in the database. Where there is a match, those rows get returned to us. An advanced use of the wildcard operator would be to search for the middle of a word. For example, if we changed our query to WHERE NameOfCat LIKE ‘Gar%h’ (notice the h at the end), we would only get names like Garth and Gareth because in this example they start with Gar, but end with h.

IN We may want to find records that match a list or a set number of possibilities. We may want to only find dogs with the names Pluto, Lassie, Benji, and Old Yeller. The keyword IN will allow us to provide a list of names to search. We separate each search criteria (in this case, name) with a comma. In this example we are using the string data type in the NameOfDog column. To use the list, I enclose the names with parentheses, and use single quotes before and after each name.

 

SELECT  NameOfDog , BreedOfDog , ColorOfDog FROM    Dog WHERE   NameOfDog IN ( 'Pluto', 'Lassie', 'Benji', 'Old         Yeller' );

OUTPUT

NameOfDog        BreedOfDog             ColorOfDog
————–            ———————-      ———————-

Pluto                      Bloodhound             Yellow Orange

Benji                      Tibetan Terrier          Black

Old Yeller              Chacy Ranior            White

Benji                      Chihuahua               Yellow

Lassie                    Border Collie            Light Brown and White

(5 row(s) affected)
Let‘s say we only wanted to see owners with an even age between 8 and 16 years old. I don‘t know why we might want this, but sometimes it is fun to poke at the data a bit. This time OwnerAge is a numeric column so we don‘t need to add the single quotes, but we do need to separate each number with a comma.

 

SELECT OwnerName, OwnerAge, OwnerGender FROM PetOwner WHERE OwnerAge IN (8,10,12,14,16);

OUTPUT

OwnerName                OwnerAge    OwnerGender
—————                   ———–         ———–

Ava                                  14                    F
Christoper                      12                   M
Steven                             14                   M
Layla                                10                   F
Terry                                10                   M

 

23 | FILTERING

Gerald                                12                  M
Keith Urbon                      14                  M
Samuel Lamanite             16                  M
Ralph Rice                         10                  M
Lawrence Arabia              12                  M
Nicholas Cage                   14                  M
Roy Orbison                      16                  M
Hailey                                 14                  F
Gary                                    16                 M
Clarence Angel                  14                 M
Sean                                    16                 M

(16 row(s) affected)
NOT IN We may want to exclude certain items from our result set. We may want to exclude the names Old Yeller and Odie. The keyword NOT IN will allow us to provide a list to exclude from the search. We separate each exclusion criteria (in this case, name) with a comma. In this example we are using the string data type in the NameOfDog column. We will enclose the name with parentheses, and use single quotes before and after each name.

SELECT NameOfDog, BreedOfDog, ColorOfDog  FROM Dogs WHERE NameOfDog NOT IN ( 'Hercules', 'Spike','Pongo','Ace','Clifford','Laddie','Rover');

OUTPUT (Modified)

NameOfDog      BreedOfDog                   ColorOfDog
————–         ————————          ———————

Odie                Abruzzenhund                  Yellow
Jack                 Bergamasco                      Tan
Jack                 Bernese Mountain Dog   Black and White
Jack                 Bernese Rottie                  Black
Jack                 Bhagyari Kutta                  White and Yellow
Pluto               Bloodhound                      Yellow Orange
Dingo             Carolina Dog                      Gray
Diogenes       Carolina Dog                      Black and White
Einstein          Bleu de Gascogne             Black
Flash               Bloodhound                      White and Yellow

(122 row(s) affected)

I

Want to follow along on your own database?

Do you want to follow along with the examples?  We have created a sample database you can use to follow along and try in your own database.  Go to the download page and follow the instructions.  Have fun on the SQL trail!