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’:

SELECT * FROM bikeshare_stations WHERE name LIKE '%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.

SELECT * FROM bikeshare_stations WHERE name LIKE '7th%' --7th at beginning of name OR name LIKE '% 7th%' --space in front of 7th

Change the program above to find the number of records that end with NW. Write the number of records found in the box below.

You have attempted of activities on this page