Standardise free-text cities

City and country were typed into a web form with no validation, so the same place is in the table a dozen ways. Normalise them before anybody groups by them.

Input

contacts contact_id bigint raw_city string -- free text; may be null, blank, or padded country string -- a two-letter code, in any case; may be null

Output

Every input row, with the columns:

contact_id
city — trimmed, every run of whitespace reduced to one space, then title case. 'UNKNOWN' if it is null or blank.
country — trimmed and upper case. 'UNKNOWN' if it is null or blank.

Example

contact_id	raw_city	country	city	country
1	`new delhi`	in	New Delhi	IN
3		us	UNKNOWN	US
4	Austin	null	Austin	UNKNOWN

Constraints

Whitespace includes tabs and newlines, and the runs that matter are the ones inside the value — trim alone does not reach those.
BENGALURU, bengaluru and BeNgAlUrU must all come out as Bengaluru.
Blank is not the same as null, and both become 'UNKNOWN'. A comparison against '' on a null column is null, not false, so the null check has to happen too.
Row order is not graded. Column names and their order are.

Community-reported interview topic. Not an official company question and no affiliation is implied.

Standardise free-text cities

Input

Output

Example

Constraints

Example 1padding and a run of spaces inside the name

Example 2blank and missing both become UNKNOWN

Topics

Similar problems

country	raw_city	contact_id
in	new delhi	1
IN	Pune	2