Ola: Registration Plates Cleaned for the Compliance Upload
Driver-partners type their vehicle's registration number themselves, in any case and with spaces or hyphens, and the compliance team must upload clean…
- Difficulty: Easy
- Topics: Strings
- Dialect: MySQL
- Problem: #829
Problem statement
Driver-partners type their vehicle's registration number themselves, in any case and with spaces or hyphens, and the compliance team must upload clean plates in one fixed format.
For every vehicle return vehicle_id, clean_plate — the plate in upper case with every space and hyphen removed (so ka 01-ab 1234 becomes KA01AB1234) — and state_code, the first two characters of the clean plate. A vehicle with no plate on file (plate is NULL) appears with NULL in both columns. Order the rows by vehicle_id.
A practice scenario set at Ola: the tables and data are invented, and this is not a question from, or endorsed by, Ola.
Tables
Table: Vehicle
| Column | Type |
|---|---|
| vehicle_id | int |
| driver_id | int |
| category | enum(auto, mini, prime_sedan, prime_suv) |
| plate | varchar |
Primary key: vehicle_id.
plate is exactly what the driver typed: letters, digits, spaces and hyphens, possibly with spaces at either end.
Examples
Example 1
Vehicle
| vehicle_id | driver_id | category | plate |
|---|---|---|---|
| 1 | 201 | auto | ka 01-ab 1234 |
| 2 | 202 | mini | MH12DE4521 |
| 3 | 203 | prime_sedan | dl-3c-af-0042 |
| 4 | 204 | prime_suv | NULL |
| 5 | 205 | mini | Ts 09 Ex 7781 |
Output
| vehicle_id | clean_plate | state_code |
|---|---|---|
| 1 | KA01AB1234 | KA |
| 2 | MH12DE4521 | MH |
| 3 | DL3CAF0042 | DL |
| 4 | NULL | NULL |
| 5 | TS09EX7781 | TS |
How to solve Ola: Registration Plates Cleaned for the Compliance Upload
Cleaning a string usually means stacking simple functions. REPLACE(plate, ' ', '') deletes every space anywhere in the plate (so a separate TRIM is not needed), a second REPLACE deletes the hyphens, and UPPER fixes the case. The order of the two REPLACEs does not matter, and upper-casing before or after removing characters gives the same result.
The state code has to be cut from the cleaned value: LEFT(raw_plate, 2) on ' dl-3c…' would return two spaces. Taking LEFT(…, 2) of the cleaned plate gives DL. Repeating the cleaning expression is fine; the alternative computes it once in a CTE and reads SUBSTRING(clean_plate, 1, 2) from it.
NULL needs no special handling: every string function returns NULL for a NULL input, so a vehicle with no plate keeps its row with NULL in both computed columns, as asked. The query is a single scan with constant work per row.
Reference solution (MySQL)
SELECT vehicle_id,
UPPER(REPLACE(REPLACE(plate, ' ', ''), '-', '')) AS clean_plate,
UPPER(LEFT(REPLACE(REPLACE(plate, ' ', ''), '-', ''), 2)) AS state_code
FROM Vehicle
ORDER BY vehicle_idAnother way
WITH c AS (SELECT vehicle_id, UCASE(REPLACE(REPLACE(TRIM(plate), '-', ''), ' ', '')) AS clean_plate FROM Vehicle) SELECT vehicle_id, clean_plate, SUBSTRING(clean_plate, 1, 2) AS state_code FROM c ORDER BY vehicle_id← Ola: Test Ride Bookings and No-Shows per Experience Centre · Ola: Scooters Due for Their First Free Service →