states.sql

CASE WHEN COALESCE(UPPER(country_name), 'OTHER_COUNTRY') = 'INDIA' AND  UPPER(region_name) in ('CHANDIGARH', 'DELHI', 'HARYANA', 'HIMANCHAL PRADESH', 'JAMMU AND KASHMIR', 'PUNJAB', 'RAJASTHAN', 'UTTAR PRADESH','UTTARAKHAND') THEN 'NORTH'
     WHEN COALESCE(UPPER(country_name), 'OTHER_COUNTRY') = 'INDIA' AND  UPPER(region_name) in ('CHHATTISGARH', 'DADAR AND NAGAR', 'DIU AND DAMAN', 'GOA', 'GUJARAT', 'MADHYA PRADESH', 'MAHARASHTRA') THEN 'WEST'
     WHEN COALESCE(UPPER(country_name), 'OTHER_COUNTRY') = 'INDIA' AND  UPPER(region_name) in ('ANDAMAN AND NICOBAR', 'ANDHRA PRADESH', 'KARNATAKA', 'KERALA', 'PONDICHERRY', 'TAMIL NADU', 'TELANGANA', 'LAKSHADWEEP') THEN 'SOUTH'
     WHEN COALESCE(UPPER(country_name), 'OTHER_COUNTRY') = 'INDIA' AND  UPPER(region_name) in ('ARUNACHAL PRADESH', 'ASSAM', 'BIHAR', 'JHARKHAND', 'MANIPUR', 'MEGHALAYA', 'MIZORAM', 'NAGALAND', 'ODISHA', 'SIKKIM', 'TRIPURA', 'WEST BENGAL') THEN 'EAST'
     WHEN COALESCE(UPPER(country_name), 'OTHER_COUNTRY') = 'OTHER_COUNTRY' OR  COALESCE(UPPER(region_name), 'OTHER_STATE') = 'OTHER_STATE' THEN 'OTHERS'
     WHEN COALESCE(UPPER(country_name), 'OTHER_COUNTRY') <> 'INDIA' THEN 'FOREIGN'
     END AS region