Matching inconsistent color names
From the sample of records in the parking_violation
table, it is clear that the vehicle_color
values are not consistent. For example, 'GRY'
, 'GRAY'
, and 'GREY'
are all used to describe a gray vehicle. In order to consistently represent this color, it is beneficial to use a single value. Fortunately, the DIFFERENCE()
function can be used to accomplish this goal.
In this exercise, you will use the DIFFERENCE()
function to return records that contain a vehicle_color
value that closely matches the string 'GRAY'
. The fuzzystrmatch
module has already been enabled for you.
This exercise is part of the course
Cleaning Data in PostgreSQL Databases
Exercise instructions
- Use the
DIFFERENCE()
function to findparking_violation
records having avehicle_color
with a Soundex code that matches the Soundex code for'GRAY'
. Recall that theDIFFERENCE()
function accepts string values (not Soundex codes) as parameter arguments.
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
summons_number,
vehicle_color
FROM
parking_violation
WHERE
-- Match SOUNDEX codes of vehicle_color and 'GRAY'
___(___, ___) = ___;