17.5. Filtering with Like¶
If the data that we are trying to filter is strings of text, we may need to search
inside the strings. Perhaps we are looking for all names that start with “Janice”,
or all class descriptions that include the word “Chemistry” anywhere in the title.
To match part of a string using
WHERE, we can’t rely on
==. If we ask for
WHERE name == 'Janice', we will only get names that perfectly match ‘Janice’ -
which would not include ‘Janice Jones’.
LIKE comparison allows us to specify strings using wildcards. Wildcards
are symbols that can match anything. In SQL
% is a wildcards
that means “any text can go here”. So
'Janice%'' says “Look for string that start with
Janice, and then have anything after that (including nothing else)”. While
says “look for string that end in Janice and may have anything before that”. If we are
want to allow for extra text to be on either side of ‘Janice’, we would use use
both sides -
This sample pulls from the
bikeshare_stations table to search for all station names
that include ‘F St’:
We do have to be careful not to specify things that are too broad. Try changing the like to look for anything that includes ‘7th’ and examine the results.
'%7th%' matches way too many things. To get just 7th (and not 17th or 27th)
we would need to state something like “where the name starts with 7th or the name has ‘ 7th’
in it”. The space inside the string will make sure we don’t match ‘17th’. This query
will perform that search. Notice that to check if the name begins with ‘7th’, we make sure
not to put a
% at the start of the string.