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
Exercise instructions
- Use
split_part()
to select the first word instreet
; alias the result asstreet_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;