Split strings on a delimiter

The street suffix is the part of the street name that gives the type of street, such as Avenue, Road, or Street. In the Evanston 311 data, sometimes the street suffix is the full word, while other times it is the abbreviation.

Extract just the first word of each street value to find the most common streets regardless of the suffix.

To do this, use

split_part(string_to_split, delimiter, part_number)

This exercise is part of the course

Exploratory Data Analysis in SQL

View Course

Exercise instructions

Use split_part() to select the first word in street; alias the result as street_name.
Also select the count of each value of street_name.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

-- Select the first word of the street value
SELECT ___ AS street_name, 
       count(*)
  FROM evanston311
 GROUP BY ___
 ORDER BY count DESC
 LIMIT 20;

Edit and Run Code