Ola: Registration Plates Cleaned for the Compliance Upload

Easy
829.

Ola: Registration Plates Cleaned for the Compliance Upload

EasyStrings

MySQL

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

ColumnTypeValues
vehicle_idint
driver_idint
categoryenumauto, mini, prime_sedan, prime_suv
platevarchar

Primary key: vehicle_id.

plate is exactly what the driver typed: letters, digits, spaces and hyphens, possibly with spaces at either end.

Example

The rows must come back in the order shown.

Vehicle

vehicle_iddriver_idcategoryplate
1201autoka 01-ab 1234
2202miniMH12DE4521
3203prime_sedan dl-3c-af-0042
4204prime_suvNULL
5205miniTs 09 Ex 7781

Output

vehicle_idclean_platestate_code
1KA01AB1234KA
2MH12DE4521MH
3DL3CAF0042DL
4NULLNULL
5TS09EX7781TS
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
vehicle_idint
driver_idint
categoryenum(auto, mini, prime_sedan, prime_suv)
platevarchar

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_iddriver_idcategoryplate
1201autoka 01-ab 1234
2202miniMH12DE4521
3203prime_sedan dl-3c-af-0042
4204prime_suvNULL
5205miniTs 09 Ex 7781

Output

vehicle_idclean_platestate_code
1KA01AB1234KA
2MH12DE4521MH
3DL3CAF0042DL
4NULLNULL
5TS09EX7781TS

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_id

Another 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 →