Trimming
Some of the street values in evanston311 include house numbers with # or / in them. In addition, some street values end in a ..
Remove the house numbers, extra punctuation, and any spaces from the beginning and end of the street values as a first attempt at cleaning up the values.
This is a part of the course
“Exploratory Data Analysis in SQL”
Exercise instructions
- Trim digits 0-9,
#,/,., and spaces from the beginning and end ofstreet. - Select distinct original
streetvalue and the correctedstreetvalue. - Order the results by the original
streetvalue.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT distinct street,
-- Trim off unwanted characters from street
trim(___, ___) AS cleaned_street
FROM evanston311
ORDER BY ___;This exercise is part of the course
Exploratory Data Analysis in SQL
Learn how to explore what's available in a database: the tables, relationships between them, and data stored in them.
Text, or character, data can get messy, but you'll learn how to deal with inconsistencies in case, spacing, and delimiters. Learn how to use a temporary table to recode messy categorical data to standardized values you can count and aggregate. Extract new variables from unstructured text as you explore help requests submitted to the city of Evanston, IL.
Exercise 1: Character data types and common issuesExercise 2: Count the categoriesExercise 3: Spotting character data problemsExercise 4: Cases and spacesExercise 5: TrimmingExercise 6: Exploring unstructured textExercise 7: Splitting and concatenating textExercise 8: Concatenate stringsExercise 9: Split strings on a delimiterExercise 10: Shorten long stringsExercise 11: Strategies for multiple transformationsExercise 12: Create an "other" categoryExercise 13: Group and recode valuesExercise 14: Create a table with indicator variablesWhat is DataCamp?
Learn the data skills you need online at your own pace—from non-coding essentials to data science and machine learning.