Standardizing multiple colors
After the success of standardizing the naming of GRAY
-colored vehicles, you decide to extend this approach to additional colors. The primary colors RED
, BLUE
, and YELLOW
will be used for extending the color name standardization approach. In this exercise, you will:
- Find
vehicle_color
values that are similar toRED
,BLUE
, orYELLOW
. - Handle both the ambiguous
vehicle_color
valueBL
and the incorrectly identifiedvehicle_color
valueBLA
using pattern matching. - Update the
vehicle_color
values with strong similarity toRED
,BLUE
, orYELLOW
to the standard string values.
This exercise is part of the course
Cleaning Data in PostgreSQL Databases
Hands-on interactive exercise
Have a go at this exercise by completing this sample code.
SELECT
summons_number,
vehicle_color,
-- Include the DIFFERENCE() value for each color
___(vehicle_color, 'RED') AS "red",
___(___, 'BLUE') AS "blue",
___(___, ___) AS "yellow"
FROM
parking_violation
WHERE
(
-- Condition records on DIFFERENCE() value of 4
DIFFERENCE(vehicle_color, 'RED') = 4 OR
___(___, ___) = ___ OR
___(___, ___) = ___
)