2

I have a MySQL database table that contains a column named Country. Sometimes it's a full CNT name, sometimes ISO 3 or ISO 2. CNT code is depending on the source file used for this table. The country names are always written in uppercase (CANADA, POLAND etc.).

I need to add some statement into my query what would convert the country name or country ISO 3 code to the ISO 2 CNT code - (CA, PL etc.).

I tried to use an easy solution:

select left (Country, 2) from tb_name

This is not useful for all countries. If I will take only the first two left letters from 'POLAND' then I will get 'PO' instead of 'PL'.

I will really appreciate any advice from you.

Kate Orlova
  • 3,225
  • 5
  • 11
  • 35
Adela
  • 21
  • 1

3 Answers3

0

As you have inconsistent names in the column, I would recommend having a lookup table like the one below:

countries
country_name | country_code
POLAND       | PL
POL          | PL
CANADA       | CA

country_name can be a primary key (and you can add more names as they appear in your other table) and country_code can be an ISO country code.

Once this is done, you can use JOIN in your queries to always get counry_code from this table.

Darshan Mehta
  • 30,102
  • 11
  • 68
  • 102
0

You will need another table, like

CuntryISO2  CountryISO3 CountryName
PL          POL         POLAND
IT          ITA         ITALY

Then you can join your table with this one, with a join like

on  myTab.Country = case(when length(myTab.Country) = 2 then CountryISO2
                         when length(myTab.Country) = 3 then CountryISO3
                         else CountryName end)
Stefano Zanini
  • 5,876
  • 2
  • 13
  • 33
0

This is too long for a comment.

Start by extracting the non-2 digit codes in your data:

select distinct country
from t
where length(country) <> 2
group by country;

Then, load this into a spreadsheet and add the two-digit code in a separate column. Wikipedia should be a fine source for the 2-digit country code.

Then you can either create a giant case statement or a table for update purposes.

But you need to begin by having the right assignment.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786