SQL How to select data (countries) that only has code with 3 characters?-CodePudding

I have to extract all the list of countries. however in the datatable under country column (2nd column), it includes continent as well, which is not what i want.

I realised those with the first column (iso_code) with only 3 characters are data with countries, while those with more than 3 characters are continents/non country. How do i go about extracting this?

enter image description here

this is my current code when extracting everything (including continent that are under country column) :

select count(distinct country) as "Number of Countries" FROM owid_energy_data [enter image description here][2]

CodePudding user response：

Like this:

select count(distinct country) as "Number of Countries" 
FROM owid_energy_data    
where len(iso_code) = 3

CodePudding user response：

SELECT OED.COUNTRY AS [NUMBER OF COUNTRIES]
FROM OWID_ENERGY_DATA OED
WHERE LEN(OED.COUNTRY) < 3

Try that!

CodePudding user response：

if you are looking to extract all the distinct names of countries from a column where you have first three letters always as a country name then use this:

select distinct left(country,3) as country from owid_energy_data

selecting the countries, where the iso_code (first column) has only three letters:

select distinct country as countries from owid_energy_data where length(iso_code) = 3

if you want the count then simply add count to one of the above:

select  count(distinct country) as total_countries from owid_energy_data