Today I had to figure out the most efficient way to generate Emoji from ISO 3166-1 Alpha-2 country codes such as US, GB, etc. I opted to go for a for 100% SQL solution.

The most efficient way to convert each ASCII letter to their Unicode Regional Indicator Symbol Letter that I determined is provided below (simplified)…

Note: 4,036,986,725 is the Unicode code point right before Regional Indicator Symbol Letter A ‘🇦’

SELECT CONCAT(CHAR(4036986725 + ASCII(SUBSTRING('US', 1, 1))), CHAR(4036986725 + ASCII(SUBSTRING('US', -1)))) AS emoji;

The actual SQL we ran to update every country in our table was similar to…

UPDATE country SET emoji = CONCAT(CHAR(4036986725 + ASCII(SUBSTRING(iso2, 1, 1))), CHAR(4036986725 + ASCII(SUBSTRING(iso2, -1))));

After combining the resulting code points ‘🇺’ and ‘🇸’ we get ‘🇺🇸’ (US flag emoji)

Required: UTF-8 connection, utf8mb4_unicode_520_ci collation