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’.
The LIKE comparison allows us to specify strings using wildcards. Wildcards
are symbols that can match anything. In SQL LIKE comparisons, % is a wildcard
that means “any text can go here”. So 'Janice%'' says “Look for strings that start with
Janice, and then have anything after that (including nothing else)”. While '%Janice'
says “look for strings that end in Janice and may have anything before that”. If we
want to allow for extra text to be on either side of ‘Janice’, we would use % on
both sides - '%Janice%'
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.
The pattern '%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’
within 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.