Get startedGet started for free

Type conversion with a CASE clause

One of the parking_violation attributes included for each record is the vehicle's location with respect to the street address of the violation. An 'F' value in the violation_in_front_of_or_opposite column indicates the vehicle was in front of the recorded address. A 'O' value indicates the vehicle was on the opposite side of the street. The column uses the TEXT type to represent the column values. The same information could be captured using a BOOLEAN (true/false) value which uses less memory.

In this exercise, you will convert violation_in_front_of_or_opposite to a BOOLEAN column named is_violation_in_front using a CASE clause. This column is true for records that occur in front of the recorded address and false for records that occur opposite of the recorded address.

This exercise is part of the course

Cleaning Data in PostgreSQL Databases

View Course

Exercise instructions

  • Include one case condition that sets the value of is_violation_in_front to true when the violation_in_front_of_or_opposite value is equal to 'F' for the record.
  • Include another case condition that sets the value of is_violation_in_front to false when the violation_in_front_of_or_opposite value is equal to 'O' for the record.

Hands-on interactive exercise

Have a go at this exercise by completing this sample code.

SELECT
  CASE WHEN
          -- Use true when column value is 'F'
          ___ = ___ THEN ___
       WHEN
          -- Use false when column value is 'O'
          ___ = ___ THEN ___
       ELSE
          NULL
  END AS ___
FROM
  parking_violation;
Edit and Run Code