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)

Deze oefening maakt deel uit van de cursus

Exploratory Data Analysis in SQL

Cursus bekijken

Oefeninstructies

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.

Praktische interactieve oefening

Probeer deze oefening eens door deze voorbeeldcode in te vullen.

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

Code bewerken en uitvoeren