Exercise

Applying functions for string cleaning

Throughout this course, we will be using a dataset with 5000 New York City parking violation records stored in the parking_violation table.

A service to provide parking violation recipients with a hard copy of the violation is being re-designed. For proper formatting of the output of the information on the report, some fields needs to be changed from the database representation. The changes are as follows:

  • For proper text alignment on the form, violation_location values must be 4 characters in length.
  • All P-U (pick-up truck) values in the vehicle_body_type column should use a general TRK value.
  • Only the first letter in each word in the street_name column should be capitalized.

The LPAD(), REPLACE(), and INITCAP() functions will be used to effect these changes.

Instructions

100 XP
  • Add '0' to the beginning of any violation_location that is less than 4 digits in length using the LPAD() function.
  • Replace 'P-U' with 'TRK' in values within the vehicle_body_type column using the REPLACE() function.
  • Ensure that only the first letter of words in the street_name column are capitalized using the INITCAP() function.