3

Please login or register to vote for this query.

(click on this box to dismiss)

Based on their filled in location.

Q&A for researchers, developers, students, teachers, and end users interested in bioinformatics

-- Which country are the users from?
-- Based on their filled in location

WITH UsersCountry AS (
SELECT
    CASE  
       
        WHEN UPPER(Location) LIKE '%AFGHANISTAN%' THEN 'Afghanistan'
          --WHEN location like ('%AF') THEN 'Afghanistan'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' AF' THEN 'Afghanistan'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',AF' THEN 'Afghanistan'
          WHEN UPPER(Location) = 'AF' THEN 'Afghanistan'
          
          WHEN UPPER(Location) LIKE '%KABUL%' THEN 'Afghanistan'
                    
          
        WHEN UPPER(Location) LIKE '%ALBANIA%' THEN 'Albania'
          -- Cannot use AL. This is Alabama.
          
          WHEN lower(Location) LIKE lower('%Tirana%')  THEN 'Albania'
          WHEN lower(Location) LIKE lower('%Tirane%')  THEN 'Albania'
          --Do not use wildcard will false positive on Titirangi, New Zealand
          
          
        WHEN UPPER(Location) LIKE '%ALGERIA%' THEN 'Algeria'
          WHEN UPPER(Location) LIKE '%ALGIERS%' THEN 'Algeria'
          WHEN lower(location) like lower('%Annaba%') THEN 'Algeria'
        
        WHEN UPPER(Location) LIKE '%ANDORRA%' THEN 'Andorra'
        
        WHEN UPPER(Location) LIKE '%ANTARCTIC%' THEN 'Antarctica'
          WHEN lower(location) like lower('%Antartic%') THEN 'Antarctica'

        -- Antigua and Barbuda
        WHEN UPPER(Location) LIKE '%ANTIGUA%' THEN 'United Kingdom'
        WHEN UPPER(Location) LIKE '%BARBUDA%' THEN 'United Kingdom'
        

        --Argentina
        WHEN lower(Location) LIKE lower('%La Plata, MD%') THEN 'United States' -- La Plata, MD'
        WHEN lower(Location) LIKE lower('%Saltaire%') THEN 'United Kingdom' -- Saltaire not Salta' 
        WHEN lower(Location) LIKE lower('%Saltash%') THEN 'United Kingdom' -- Saltash not Salta'
        
        WHEN UPPER(Location) LIKE '%ARGENTINA%' THEN 'Argentina'
          WHEN Location LIKE '%ARG' THEN 'Argentina'
          
          WHEN lower(Location) LIKE lower('%Bah%a Blanca%') THEN 'Argentina'
          WHEN lower(Location) LIKE lower('%Berisso%') THEN 'Argentina'
          WHEN lower(Location) LIKE lower('%Buenos Aires%') THEN 'Argentina'
          
          WHEN lower(Location) LIKE lower('%C%rdoba%') THEN 'Argentina'
          WHEN lower(Location) LIKE lower('%Corrientes%') THEN 'Argentina'
          
          WHEN lower(Location) LIKE lower('%La Plata%') THEN 'Argentina'
          
          WHEN lower(Location) LIKE lower('%Mar del Plata%') THEN 'Argentina'
          WHEN lower(Location) LIKE lower('%Mendoza%') THEN 'Argentina'
          WHEN lower(Location) LIKE lower('%Neuqu%n%') THEN 'Argentina'
          WHEN lower(Location) LIKE lower('%Quilmes%') THEN 'Argentina'
          WHEN lower(Location) LIKE lower('%Rosario%') THEN 'Argentina'
          WHEN lower(Location) LIKE lower('%Salta%') THEN 'Argentina'
          WHEN lower(Location) LIKE lower('%Tucuman%') THEN 'Argentina'
          
        
        WHEN lower(Location) LIKE lower('%Colombia%') THEN 'Colombia' -- Armenia, Quind%o, Colombia
        WHEN lower(Location) LIKE lower('%Quind%o%') THEN 'Colombia' -- Armenia, Quind%o, Colombia
        
        WHEN UPPER(Location) LIKE '%ARMENIA%' THEN 'Armenia'
          WHEN UPPER(Location) LIKE '%YEREVAN%' THEN 'Armenia'
          

        -- Australia
        WHEN lower(location) like lower('%Australia%') THEN 'Australia'
          WHEN Location = 'AAU' THEN 'Unknown - Seen'
          WHEN Location LIKE '%AU' THEN 'Australia'
          WHEN Location LIKE '%AUS' THEN 'Australia'
          
          WHEN lower(location) like lower('%New South Wales%') THEN 'Australia'
          WHEN lower(location) like lower('%NSW%') THEN 'Australia'
          
          WHEN lower(Location) LIKE lower('%Queensland%') THEN 'Australia'
          WHEN lower(Location) LIKE lower('%QLD%') THEN 'Australia'
          
          WHEN lower(Location) LIKE lower('%South Australia%') THEN 'Australia'
          
          WHEN lower(Location) LIKE lower('%TASMANIA%') THEN 'Australia'
          WHEN lower(Location) LIKE lower('%Western Australia%') THEN 'Australia'
                    
          WHEN lower(location) like lower('%Adelaid%') THEN 'Australia'
          
          WHEN lower(Location) LIKE lower('%Brisbane, CA%') THEN 'United States'
          WHEN lower(Location) LIKE lower('%Brisbane%') THEN 'Australia'
          
          WHEN lower(Location) LIKE lower('%Canberra%') THEN 'Australia'
          
          WHEN UPPER(Location) LIKE '%GOLD COAST%' THEN 'Australia'

          WHEN lower(Location) LIKE lower('%Melbourne Beach, FL%') THEN 'United States'
          WHEN lower(Location) LIKE lower('%Melbourne%') THEN 'Australia'
          
          WHEN lower(location) like lower('%Sunshine Coast%') THEN 'Australia'
          
          WHEN lower(Location) LIKE lower('%Sydney, Canada%') THEN 'Canada'
          WHEN UPPER(Location) LIKE '%SYDNEY%' THEN 'Australia'
          
        -- Austria
        WHEN lower(Location) LIKE '%austria%' THEN 'Austria'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' AT' THEN 'Austria'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',AT' THEN 'Austria'
          WHEN UPPER(Location) = 'AT' THEN 'Austria'
            
        WHEN lower(Location) LIKE lower('%Haidian%') THEN 'China'--Haidian District, China (Haid)
        
        WHEN lower(Location) LIKE lower('%Bellinzago%') THEN 'Italy' --Bellinzago Novarese, Italy (Linz)
        WHEN lower(Location) LIKE lower('%South%Tyrol%') THEN 'Italy'-- South Tyrol, Italy
            
        WHEN lower(Location) LIKE lower('%Thailand%') THEN 'Thailand' -- Thail
          
        WHEN lower(Location) LIKE lower('%Amstetten%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Ansfelden%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Anthering%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Braunau%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Bregenz%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Burgenland%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Carinthia%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Dornbirn%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Eugendorf%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Feldkirch%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Freistadt%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Gleisdorf%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Gofis%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Graz%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Grundlsee%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Guntramsdorf%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Hagenberg%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Haid%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Hallein%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Innsbruck%') THEN 'Austria'

        WHEN lower(Location) LIKE lower('%Kitzbuhel%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Klagenfurt%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Klosterneuburg%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Kremsdorf%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Kumberg%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Leoben%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Leonding%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Linz%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Messnerhaus%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Millstatt am See%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%M%dling%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Neustadt%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Oftering%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Pressbaum%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Saalfelden%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Salzburg%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Schwechat%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Seiersberg%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Siegenfeld%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Sierning%') THEN 'Austria'
            --Sankt Pölten and St. Poelten and St. Polten
        WHEN lower(Location) LIKE lower('%S%P%lten%') THEN 'Austria' 
        WHEN lower(Location) LIKE lower('%Steyr%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Stiwol%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Styria%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Thail%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Tirol%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Tyrol%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Unterweitersdorf%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Vienna%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Villach%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Vorarlberg%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Waidring%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Weistrach%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Wels%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Wien%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%Wolfsberg%') THEN 'Austria'
        WHEN lower(Location) LIKE lower('%W%rdern%') THEN 'Austria'
      
        WHEN lower(Location) LIKE lower('%Zell am See%') THEN 'Austria'
        
        
      -- Azerbaijan
        WHEN lower(Location) LIKE lower('%Azerbaijan%Iran%') THEN 'Iran'
        WHEN UPPER(Location) LIKE '%AZERBAIJAN%' THEN 'Azerbaijan'
          WHEN UPPER(Location) LIKE '%MEMBAKUT%' THEN 'Malaysia'
          WHEN UPPER(Location) LIKE '%BAKU%' THEN 'Azerbaijan'
          
          
        WHEN UPPER(Location) LIKE '%BAHAMAS%' THEN 'Bahamas'
        WHEN UPPER(Location) LIKE '%BAHRAIN%' THEN 'Bahrain'
        --WHEN UPPER(Location) LIKE '%BANGLADESH%' THEN 'Bangladesh'
        
        
        WHEN lower(Location) LIKE ('%bangladesh%') THEN 'Bangladesh'
          WHEN Location LIKE  '%BD' THEN 'Bangladesh'
          
          WHEN lower(Location) LIKE ('%barisal%') THEN 'Bangladesh'
          WHEN lower(Location) LIKE ('%bogra%') THEN 'Bangladesh'
               
          WHEN lower(Location) LIKE ('%chittagong%') THEN 'Bangladesh'
          WHEN lower(Location) LIKE ('%comilla%') THEN 'Bangladesh'
               
          WHEN lower(Location) LIKE ('%dhaka%') THEN 'Bangladesh'
          WHEN lower(Location) LIKE ('%dinajpur%') THEN 'Bangladesh'
               
          WHEN lower(Location) LIKE ('%feni%') THEN 'Bangladesh'
               
          WHEN lower(Location) LIKE ('%gafargaon%') THEN 'Bangladesh'
          WHEN lower(Location) LIKE ('%gaibandha%') THEN 'Bangladesh'
               
          WHEN lower(Location) LIKE ('%jamalpur%') THEN 'Bangladesh'
          WHEN lower(Location) LIKE ('%jessore%') THEN 'Bangladesh'
               
          WHEN lower(Location) LIKE ('%khulna%') THEN 'Bangladesh'
          WHEN lower(Location) LIKE ('%kushtia%') THEN 'Bangladesh'
               
          WHEN lower(Location) LIKE ('%mirpur%') THEN 'Bangladesh'
          WHEN lower(Location) LIKE ('%mymensingh%') THEN 'Bangladesh'
               
          WHEN lower(Location) LIKE ('%naogaon%') THEN 'Bangladesh'
               
          WHEN lower(Location) LIKE ('%rajshahi%') THEN 'Bangladesh'
          WHEN lower(Location) LIKE ('%rangpur%') THEN 'Bangladesh'
               
          WHEN lower(Location) LIKE ('%sylhet%') THEN 'Bangladesh'
               
               
        WHEN UPPER(Location) LIKE '%BARBADOS%' THEN 'Barbados'
        
        
        -- Belarus
        WHEN UPPER(Location) LIKE '%BELARUS%' THEN 'Belarus'
          WHEN lower(Location) LIKE lower('%Fominsk%')  THEN 'Russia' -- Naro-Fominsk, Russia
          
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' BY' THEN 'Belarus'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',BY' THEN 'Belarus'
          WHEN UPPER(Location) = 'BY' THEN 'Belarus'
      
          WHEN lower(Location) LIKE lower('%Minsk%') THEN 'Belarus'
          WHEN lower(Location) LIKE lower('%Vitebk%') THEN 'Belarus'
          -- includes Vitsyebskaya Voblasts
          WHEN lower(Location) LIKE lower('%Vitsyebsk%')  THEN 'Belarus'
          

        --WHEN UPPER(Location) LIKE '%BELGIUM%' THEN 'Belgium'
         -- WHEN UPPER(Location) LIKE '%BRUXELLES%' THEN 'Belgium'
          --WHEN UPPER(Location) LIKE '%BRUSSELS%' THEN 'Belgium'
          
        WHEN lower(Location) LIKE lower('%Belgium%') THEN 'Belgium'
        
          -- many false positives, for example "to be"
          --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' BE'  THEN 'Belgium'
          WHEN UPPER(RIGHT(RTRIM(Location), 4)) = ', BE' THEN 'Belgium'
      
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',BE' THEN 'Belgium'
          WHEN UPPER(Location) = 'BE' THEN 'Belgium'
      
          WHEN lower(Location) = lower('%Antwerp%') THEN 'Belgium' -- Assumed Belgium
          WHEN lower(Location) like lower('%Antwerpen%') THEN 'Belgium'
      
          WHEN lower(Location) LIKE lower('%Bruges%') THEN 'Belgium'
          WHEN lower(Location) LIKE lower('%Bruxelles%') THEN 'Belgium'
          WHEN lower(Location) LIKE lower('%Brussels%') THEN 'Belgium'
      
          WHEN lower(Location) LIKE lower('%Dilsen%') THEN 'Belgium'
      
          WHEN lower(Location) LIKE lower('%st%Flanders%') THEN 'Belgium' -- East and West Flanders
      
          WHEN lower(Location) = lower('Ghent') THEN 'Belgium' -- Assumed Belgium
            WHEN lower(Location) LIKE lower('%Lokeren%Ghent%') THEN 'Belgium'
            WHEN lower(Location) LIKE lower('%Ghent%EU%') THEN 'Belgium'
            WHEN lower(Location) LIKE lower('%Ghent%Flanders%') THEN 'Belgium'
      
          WHEN lower(Location) LIKE lower('%Flemish Region%') THEN 'Belgium'  -- Assumed Belgium
      
          WHEN lower(Location) = lower('Hasselt') THEN 'Belgium'  -- Assumed Belgium
          WHEN lower(Location) = lower('Hoogstraten') THEN 'Belgium'  -- Assumed Belgium
      
          WHEN lower(Location) LIKE lower('%Ieper%') THEN 'Belgium' -- Ypres  -- Assumed Belgium
      
          WHEN lower(Location) = lower('Kortrijk') THEN 'Belgium' -- Assumed Belgium
      
          WHEN lower(Location) LIKE lower('%Leuven%') THEN 'Belgium'
          WHEN lower(Location) LIKE lower('%Lokeren%') THEN 'Belgium'
      
          WHEN lower(Location) LIKE lower('%Mechelen%') THEN 'Belgium'
          WHEN lower(Location) = lower('Mons') THEN 'Belgium' -- Assumed Belgium
      
          WHEN lower(Location) LIKE lower('%Namur%') THEN 'Belgium'
      
          WHEN lower(Location) LIKE lower('%st%Vlaanderen%') THEN 'Belgium' -- Oost and West
          WHEN lower(Location) = lower('Vilvoorde') THEN 'Belgium' -- Assumed Belgium
      
          WHEN lower(Location) LIKE lower('%Waregem%') THEN 'Belgium'

          WHEN lower(Location) = lower('Ypres') THEN 'Belgium'
          
      
        WHEN UPPER(Location) LIKE '%BELIZE%' THEN 'Belize'
        WHEN UPPER(Location) LIKE '%BENIN%' THEN 'Benin'
        WHEN UPPER(Location) LIKE '%BERMUDA%' THEN 'Bermuda'
        WHEN UPPER(Location) LIKE '%BHUTAN%' THEN 'Bhutan'
        
        -- Bolivia
        WHEN lower(Location) LIKE lower('%Bolivia, NC%') THEN 'United States'
        WHEN UPPER(Location) LIKE '%BOLIVIA%' THEN 'Bolivia'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' BO' THEN 'Bolivia'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',BO' THEN 'Bolivia'
          WHEN UPPER(Location) = 'BO' THEN 'Bolivia'
      
          WHEN lower(Location) like lower('%Cochabamba%') THEN 'Bolivia'
          WHEN lower(Location) like lower('%Santa Cruz de la Sierra%') THEN 'Bolivia'
          WHEN lower(Location) like lower('%Sucre%') THEN 'Bolivia'
          WHEN lower(Location) like lower('%Tarija%') THEN 'Bolivia'
          WHEN lower(Location) like lower('%Uyuni%') THEN 'Bolivia'
          
       
        WHEN UPPER(Location) LIKE '%BOSNIA%' THEN 'Bosnia and Herzegovina'
        
        WHEN lower(Location) LIKE lower('%Botswana%') THEN 'Botswana'
          WHEN lower(Location) = lower('Francistown') THEN 'Botswana'
          WHEN lower(Location) = lower('Gaborone') THEN 'Botswana'

        
        -- Brazil
        WHEN lower(Location) LIKE '%arrecife%' THEN 'Spain' 
        WHEN lower(Location) LIKE '%brazil, in%'THEN 'United States' 
                 
        WHEN lower(Location) LIKE lower('%Brazil%') THEN 'Brazil'
        WHEN lower(Location) LIKE lower('%Brasil%') THEN 'Brazil'
          
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' BR' THEN 'Brazil'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',BR' THEN 'Brazil'
          WHEN UPPER(Location) = 'BR' THEN 'Brazil'
          
          WHEN lower(Location) = lower('Amazonas') THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%aracaju%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%araraquara%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%bauru%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%belem%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%belo horizonte%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%blumenau%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%brasilia%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%brasília%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%campina grande%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%campinas%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%campo grande%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%cascavel%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%caxias do sul%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%curitiba%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE lower('%Esp%rito Santo%') THEN 'Brazil'
          
          WHEN lower(Location) LIKE '%florian%polis%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%fortaleza%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%goiania%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%joao pessoa%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%joinville%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%juiz de fora%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%jundiai%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%londrina%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%manaus%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%maringa%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%maringá%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%osasco%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%porto alegre%' THEN 'Brazil'
               
          WHEN lower(Location) LIKE '%recife%' THEN 'Brazil'
          WHEN lower(Location) LIKE '%rio de janeiro%' THEN 'Brazil'
              
          WHEN lower(Location) LIKE '%s%o paulo%' THEN 'Brazil'
          

        WHEN UPPER(Location) LIKE '%BRUNEI%' THEN 'Brunei'
        
        WHEN lower(Location) LIKE lower('%Bulgaria%') THEN 'Bulgaria'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' BG' THEN 'Bulgaria'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',BG' THEN 'Bulgaria'
          WHEN UPPER(Location) = 'BG' THEN 'Bulgaria'
      
          WHEN lower(Location) = lower('Plovdiv') THEN 'Bulgaria'
          WHEN lower(Location) = lower('Sofia') THEN 'Bulgaria'
          WHEN lower(Location) = lower('Varna') THEN 'Bulgaria'

        WHEN UPPER(Location) LIKE '%CAMBODIA%' THEN 'Cambodia'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' KH' THEN 'Cambodia'
          WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',KH' THEN 'Cambodia'
          WHEN UPPER(Location) = 'KH' THEN 'Cambodia'
          
          WHEN lower(Location) LIKE lower('%Kampot%') THEN 'Cambodia'
          WHEN lower(Location) LIKE lower('%Phnom Penh%') THEN 'Cambodia'

        WHEN UPPER(Location) LIKE '%CAMEROON%' THEN 'Cameroon'
        WHEN UPPER(Location) LIKE '%YAOUND%' THEN 'Cameroon' --Yaoundé
        

        -- Canada     
        WHEN lower(Location) LIKE lower('%Canada%') THEN 'Canada'
        WHEN lower(Location) LIKE lower('%Canadur%') THEN 'Canada'
        
        WHEN lower(Location) LIKE lower('%California%') THEN 'United States'

          WHEN lower(Location) LIKE lower('%Alberta%') THEN 'Canada'
            WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' AB' THEN 'Canada'
            WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',AB' THEN 'Canada'
            WHEN UPPER(Location) = 'AB' THEN 'Canada'
            
            WHEN lower(Location) LIKE lower('%Calgary%') THEN 'Canada'
            WHEN lower(Location) LIKE lower('%Edmonton%') THEN 'Canada'
            
          WHEN UPPER(Location) LIKE '%BRITISH COLUMBIA%' THEN 'Canada'
            
            --Mexicali, Baja California, Mexico
            WHEN UPPER(Location) LIKE '%MEXICALI%' THEN 'Mexico'
            WHEN lower(Location) LIKE lower('%Mexico%') THEN 'Mexico'
            
            WHEN UPPER(Location) LIKE '%B.C.%' THEN 'Canada'
            
            WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' BC' THEN 'Canada'
            WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',BC' THEN 'Canada'
            WHEN UPPER(Location) = 'BC' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%abbotsford%' THEN 'Canada'
            WHEN lower(Location) LIKE '%burnaby%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%kamloops%' THEN 'Canada'
            WHEN lower(Location) LIKE '%kelowna%' THEN 'Canada'
            
            --WHEN UPPER(Location) LIKE '%VANCOUVER, CAN%' THEN 'Canada'
            --WHEN UPPER(Location) LIKE '%VANCOUVER,CAN%' THEN 'Canada'
            --WHEN UPPER(Location) LIKE '%VANCOUVER CAN%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%VANCOUVER, CA' THEN 'United States'
            WHEN UPPER(Location) LIKE '%VANCOUVER,CA' THEN 'United States'
            WHEN UPPER(Location) LIKE '%VANCOUVER CA' THEN 'United States'
                        
            WHEN UPPER(Location) LIKE '%VANCOUVER, WA%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%VANCOUVER,WA%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%VANCOUVER WA%' THEN 'United States'
            
            WHEN UPPER(Location) LIKE '%VANCOUVER%' THEN 'Canada'

        
          WHEN UPPER(Location) LIKE '%MANITOBA%' THEN 'Canada'
            WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' MB' THEN 'Canada'
            WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',MB' THEN 'Canada'
            WHEN UPPER(Location) = 'MB' THEN 'Canada'
                       
            WHEN lower(Location) LIKE lower('%Winnipeg%')THEN 'Canada'
            WHEN lower(Location) LIKE lower('%Winterpeg%') THEN 'Canada'
            
            
            WHEN lower(location) like '%new brunswick, nj%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%NEW BRUNSWICK%' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' NB' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',NB' THEN 'Canada'
              WHEN UPPER(Location) = 'NB' THEN 'Canada'
              
              WHEN lower(location) like '%fredericton%' THEN 'Canada'
              WHEN lower(location) like '%moncton%' THEN 'Canada'
              
              WHEN lower(Location) LIKE '%saint john n%' THEN 'Canada'
              WHEN lower(Location) LIKE '%saint john, n%' THEN 'Canada'
              WHEN lower(Location) LIKE '%saint john,n%' THEN 'Canada'
      
              WHEN lower(Location) LIKE '%saint john ca%' THEN 'Canada'
              WHEN lower(Location) LIKE '%saint john, ca%' THEN 'Canada'
              WHEN lower(Location) LIKE '%saint john,ca%' THEN 'Canada'
              
              
           
           WHEN UPPER(Location) LIKE '%NEWFOUNDLAND%' THEN 'Canada'
              --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' NL' THEN 'Canada or Netherlands'
              --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',NL' THEN 'Canada or Netherlands'
              --WHEN UPPER(Location) = 'NL' THEN 'Canada or Netherlands'
              
              WHEN lower(Location) LIKE '%st%john''s%' THEN 'Canada'
              
            WHEN UPPER(Location) LIKE '%NORTHWEST TERR%' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' NT' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',NT' THEN 'Canada'
              WHEN UPPER(Location) = 'NT' THEN 'Canada'
              
              WHEN lower(Location) LIKE '%yellowknife%' THEN 'Canada'
              
            
            WHEN UPPER(Location) LIKE '%NOVA SCOTIA%' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' NS' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',NS' THEN 'Canada'
              WHEN UPPER(Location) = 'NS' THEN 'Canada'
              
              WHEN lower(Location) LIKE lower('%HALIFAX%NS%') THEN 'Canada'
              WHEN lower(Location) LIKE lower('%HALIFAX%Nova%') THEN 'Canada'
              WHEN lower(Location) LIKE lower('%HALIFAX%CA%') THEN 'Canada'
              
              WHEN lower(Location) LIKE lower('%Sydney%Ca%') THEN 'Canada'
              
              WHEN lower(Location) LIKE '%wolfville%' THEN 'Canada'
            
            
            WHEN UPPER(Location) LIKE '%NUNAVUT%' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' NU' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',NU' THEN 'Canada'
              WHEN UPPER(Location) = 'NU' THEN 'Canada'
              
              
            WHEN lower(Location) LIKE '%california%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%ONTARIO, CA' THEN 'United States'
            WHEN UPPER(Location) LIKE '%ONTARIO,CA' THEN 'United States'
            WHEN UPPER(Location) LIKE '%ONTARIO CA' THEN 'United States'
            
            WHEN UPPER(Location) LIKE '%ONTARIO, CAL%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%ONTARIO,CAL%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%ONTARIO CAL%' THEN 'United States'
                       
            WHEN UPPER(Location) LIKE '%ONTARIO, US%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%ONTARIO,US%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%ONTARIO US%' THEN 'United States'
            
            WHEN UPPER(Location) LIKE '%ONTARIO, U.S%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%ONTARIO,U.S%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%ONTARIO U.S%' THEN 'United States'
            
            --WHEN UPPER(Location) LIKE '%ONTARIO, CAN%' THEN 'Canada'
            --WHEN UPPER(Location) LIKE '%ONTARIO,CAN%' THEN 'Canada'
            --WHEN UPPER(Location) LIKE '%ONTARIO CAN%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%ONTARIO%' THEN 'Canada'
            
            WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' ON' THEN 'Canada'
            WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',ON' THEN 'Canada'
            WHEN UPPER(Location) = 'ON' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%acton on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%acton, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%acton,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%acton can%' THEN 'Canada'
            WHEN lower(Location) LIKE '%acton, can%' THEN 'Canada'
            WHEN lower(Location) LIKE '%acton,can%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%ajax%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%aurora on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%aurora, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%aurora,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%aurora ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%aurora, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%aurora,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%barrie%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%belleville on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%belleville, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%belleville,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%belleville ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%belleville, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%belleville,ca%' THEN 'Canada'
     
            WHEN lower(Location) LIKE '%bowmanville%' THEN 'Canada'
            WHEN lower(Location) LIKE '%brampton%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%brantford%' THEN 'Canada'
            WHEN lower(Location) LIKE '%brockville%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%burlington on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%burlington, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%burlington,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%burlington ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%burlington, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%burlington,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%caledon on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%caledon, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%caledon,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%caledon ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%caledon, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%caledon,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%cambridge on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%cambridge, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%cambridge,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%cambridge ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%cambridge, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%cambridge,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%chatham on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%chatham, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%chatham,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%chatham ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%chatham, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%chatham,ca%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%chatham-kent%' THEN 'Canada'
            WHEN lower(Location) LIKE '%coe hill%' THEN 'Canada'
            WHEN lower(Location) LIKE '%collingwood%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%elmira on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%elmira, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%elmira,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%elmira ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%elmira, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%elmira,ca%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%fort erie%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%GUELPH%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%HAMILTON, ON%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%HAMILTON,ON%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%HAMILTON ON%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%HAMILTON, CA%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%HAMILTON,CA%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%HAMILTON CA%' THEN 'Canada'
            
            WHEN lower(Location) LIKE lower('%Kingston, ON%') THEN 'Canada'
            WHEN lower(Location) LIKE lower('%Kingston,ON%') THEN 'Canada'
            WHEN lower(Location) LIKE lower('%Kingston ON%') THEN 'Canada'
            
            WHEN lower(Location) LIKE lower('%Kingston, CA%') THEN 'Canada'
            WHEN lower(Location) LIKE lower('%Kingston,CA%') THEN 'Canada'
            WHEN lower(Location) LIKE lower('%Kingston CA%') THEN 'Canada'
            
            WHEN lower(Location) LIKE '%kitchener%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%kw on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%kw, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%kw,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%leamington on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%leamington, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%leamington,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%leamington ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%leamington, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%leamington,ca%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%LONDON, ON%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%LONDON,ON%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%LONDON ON%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%LONDON, CAN%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%LONDON,CAN%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%LONDON CAN%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%MARKHAM%IL%' THEN 'United States'
                        
            WHEN lower(Location) LIKE '%markham%on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%markham%ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%milton on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%milton, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%milton,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%milton ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%milton, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%milton,ca%' THEN 'Canada'
      
            WHEN UPPER(Location) LIKE '%MISSISSAUGA%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%newmarket on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%newmarket, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%newmarket,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%newmarket ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%newmarket, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%newmarket,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%north bay on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%north bay, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%north bay,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%north bay can%' THEN 'Canada'
            WHEN lower(Location) LIKE '%north bay, can%' THEN 'Canada'
            WHEN lower(Location) LIKE '%north bay,can%' THEN 'Canada'
            -- north bay, California
            
            WHEN lower(Location) LIKE '%oakville on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%oakville, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%oakville,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%oakville ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%oakville, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%oakville,ca%' THEN 'Canada'
            
            --WHEN UPPER(Location) LIKE '%OTTAWA, ON%' THEN 'Canada'
            --WHEN UPPER(Location) LIKE '%OTTAWA,ON%' THEN 'Canada'
            --WHEN UPPER(Location) LIKE '%OTTAWA ON%' THEN 'Canada'
            
            --WHEN UPPER(Location) LIKE '%OTTAWA, CAN%' THEN 'Canada'
            --WHEN UPPER(Location) LIKE '%OTTAWA,CAN%' THEN 'Canada'
            --WHEN UPPER(Location) LIKE '%OTTAWA CAN%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%OTTAWA%IL%' THEN 'United States'
            
            WHEN lower(Location) LIKE lower('%Kottawa%') THEN 'Sri Lanka'
            WHEN lower(Location) LIKE '%ottawa lake%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%OTTAWA%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%perth on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%perth, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%perth,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%perth ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%perth, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%perth,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%peterborough on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%peterborough, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%peterborough,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%peterborough ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%peterborough, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%peterborough,ca%' THEN 'Canada'
                       
            WHEN UPPER(Location) LIKE '%PETERBOROUGH, NH%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%PETERBOROUGH,NH%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%PETERBOROUGH NH%' THEN 'United States'
            
            WHEN UPPER(Location) LIKE '%PETERBOROUGH, US%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%PETERBOROUGH,US%' THEN 'United States'
            WHEN UPPER(Location) LIKE '%PETERBOROUGH US%' THEN 'United States'
            
            WHEN lower(Location) LIKE '%pickering on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%pickering, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%pickering,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%pickering ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%pickering, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%pickering,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%richmond hill on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%richmond hill, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%richmond hill,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%richmond hill ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%richmond hill, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%richmond hill,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%sarnia%' THEN 'Canada'
            
            --Sault Ste. Marie / Sault Ste Marie 
            WHEN lower(Location) LIKE '% marie on%' THEN 'Canada'
            WHEN lower(Location) LIKE '% marie, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '% marie,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '% marie ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '% marie, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '% marie,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%st. catharines%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%st. thomas%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%stratford on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%stratford, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%stratford,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%stratford ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%stratford, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%stratford,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%sudbury on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%sudbury, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%sudbury,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%sudbury ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%sudbury, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%sudbury,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%thunder bay%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%timmins on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%timmins, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%timmins,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%timmins ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%timmins, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%timmins,ca%' THEN 'Canada'
      
      
            WHEN UPPER(Location) LIKE '%TORONTO%' THEN 'Canada'
            WHEN Location LIKE '%GTA%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%VAUGHAN%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%university of waterloo%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%waterloo on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%waterloo, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%waterloo,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%waterloo ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%waterloo, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%waterloo,ca%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%welland%' THEN 'Canada'
            WHEN lower(Location) LIKE '%whitby%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%WINDSOR, ON%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%WINDSOR,ON%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%WINDSOR ON%' THEN 'Canada'
            
            WHEN UPPER(Location) LIKE '%WINDSOR, CA%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%WINDSOR,CA%' THEN 'Canada'
            WHEN UPPER(Location) LIKE '%WINDSOR CA%' THEN 'Canada'
            
            WHEN lower(Location) LIKE '%woodbridge on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%woodbridge, on%' THEN 'Canada'
            WHEN lower(Location) LIKE '%woodbridge,on%' THEN 'Canada'
      
            WHEN lower(Location) LIKE '%woodbridge ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%woodbridge, ca%' THEN 'Canada'
            WHEN lower(Location) LIKE '%woodbridge,ca%' THEN 'Canada'
      
              
            WHEN UPPER(Location) LIKE '%PRINCE EDWARD IS%' THEN 'Canada'
              WHEN UPPER(Location) LIKE '%PEI' THEN 'Canada'
              --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' PE' THEN 'Canada or Peru'
              --WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',PE' THEN 'Canada or Peru'
              --WHEN UPPER(Location) = 'PE' THEN 'Canada or Peru'
              
              WHEN lower(Location) LIKE '%charlottetown%' THEN 'Canada'
              WHEN lower(Location) LIKE '%charlottetown%' THEN 'Canada'
              WHEN lower(Location) LIKE '%charlottetown%' THEN 'Canada'
              
            WHEN lower(Location) LIKE lower('%Qu%bec%') THEN 'Canada'
            
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' QC' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',QC' THEN 'Canada'
              WHEN UPPER(Location) = 'QC' THEN 'Canada'
              
              WHEN UPPER(Location) LIKE '%QC%' THEN 'Canada'
              
              WHEN UPPER(Location) LIKE '%CHICOUTIMI%' THEN 'Canada'
              WHEN lower(Location) LIKE '%gatineau%' THEN 'Canada'
              WHEN lower(Location) LIKE lower('%Montr%al%') THEN 'Canada'
              
              WHEN lower(Location) LIKE '%sherbrooke%' THEN 'Canada'
              
            WHEN UPPER(Location) LIKE '%SASK' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' SK' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',SK' THEN 'Canada'
              WHEN UPPER(Location) = 'SK' THEN 'Canada'
              
              WHEN lower(Location) LIKE '%regina%' THEN 'Canada'
              WHEN lower(Location) LIKE '%saskatoon%' THEN 'Canada'
              
              
            WHEN UPPER(Location) LIKE '%YUKON%' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ' YK' THEN 'Canada'
              WHEN UPPER(RIGHT(RTRIM(Location), 3)) = ',YK' THEN 'Canada'
              WHEN UPPER(Location) = 'YK' THEN 'Canada'
               
        WHEN UPPER(Location) LIKE '%CAYMAN ISLANDS%' THEN 'Cayman Islands'
           
        WHEN UPPER(Location) LIKE '%CHILE%' THEN 'Chile'
          WHEN UPPER(Location) LIKE '%EASTER ISLAND%' THEN 'Chile'

        -- China
        WHEN UPPER(Location) LIKE '%CHINA%' THEN 'China'
          WHEN UPPER(Location) LIKE '%BEIJING%' THEN 'China'
          WHEN UPPER(Location) LIKE '%SHANDONG%' THEN 'China'
          WHEN UPPER(Location) LIKE '%SHANGHAI%' THEN 'China'
          WHEN UPPER(Location) LIKE '%ZHEJIANG%' THEN 'China'

        WHEN UPPER(Location) LIKE '%COLOMBIA%' THEN 'Colombia'
          WHEN lower(Location) = lower('Cali') THEN 'Colombia'
          WHEN UPPER(Location) LIKE '%SINCELEJO%' THEN 'Colombia'
          

        WHEN UPPER(Location) LIKE '%CONGO%' THEN 'Congo, Republic of the'
        WHEN UPPER(Location) LIKE '%COSTA RICA%' THEN 'Costa Rica'
        WHEN UPPER(Location) LIKE '%CROATIA%' THEN 'Croatia'
        
        WHEN UPPER(Location) LIKE '%CUBA%' THEN 'Cuba'
          WHEN lower(Location) LIKE lower('%Havana%') THEN 'Cuba'
          WHEN lower(Location) LIKE lower('%Villa Panamericana%') THEN 'Cuba'
                  
        WHEN UPPER(Location) LIKE '%CYPRUS%' THEN 'Cyprus'
          WHEN lower(Location) LIKE lower('%Limassol%') THEN 'Cyprus'
        
        WHEN UPPER(Location) LIKE '%CZECH%' THEN 'Czech Republic '
          WHEN UPPER(Location) LIKE '%PRAGUE%' THEN 'Czech Republic '

        WHEN UPPER(Location) LIKE '%DENMARK%' THEN 'Denmark'
          WHEN UPPER(Location) LIKE '%GREENLAND%' THEN 'Denmark'

        WHEN UPPER(Location) LIKE '%DOMINICAN REPUBLIC%' THEN 'Dominican Republic'                

        WHEN UPPER(Location) LIKE '%ECUADOR%' THEN 'Ecuador'
        
        WHEN UPPER(Location) LIKE '%EGYPT%' THEN 'Egypt'
          WHEN UPPER(Location) LIKE '%CAIRO%' THEN 'Egypt'
          
        WHEN UPPER(Location) LIKE '%EL SALVADOR%' THEN 'El Salvador'
        WHEN UPPER(Location) LIKE '%ESTONIA%' THEN 'Estonia'
        WHEN UPPER(Location) LIKE '%ETHIOPIA%' THEN 'Ethiopia'

        WHEN UPPER(Location) LIKE '%FAROE ISLANDS%' THEN 'Faroe Islands'
        WHEN UPPER(Location) LIKE '%FIJI%' THEN 'Fiji'
        
        WHEN UPPER(Location) LIKE '%FINLAND%' THEN 'Finland'
          WHEN UPPER(Location) LIKE '%HELSINKI%' THEN 'Finland'
          

        -- France
        WHEN lower(Location) LIKE lower('%Paris, TN%') THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%MARTINIQUE%' THEN 'France'
        
        WHEN UPPER(Location) LIKE '%FRANCE%' THEN 'France'
          WHEN lower(Location) LIKE lower('%Calais%') THEN 'France'
          WHEN UPPER(Location) LIKE '%CORSICA%' THEN 'France'
          WHEN UPPER(Location) LIKE '%FRENCH RIVIERA%' THEN 'France'
          WHEN UPPER(Location) LIKE '%LYON%' THEN 'France'
          WHEN UPPER(Location) LIKE '%PARIS%' THEN 'France'
          
          
        -- Georgia
        WHEN Location LIKE '%AC, GER' THEN 'Unknown - Seen'
        
        WHEN Location LIKE '%, GE' THEN 'Georgia'
        WHEN Location LIKE '%,GE ' THEN 'Georgia'
        WHEN Location LIKE '%GE' THEN 'Georgia'
        
        WHEN UPPER(Location) LIKE '%TBILISI%' THEN 'Georgia'
        WHEN UPPER(Location) LIKE '%KHELVACHAURI%' THEN 'Georgia'
        WHEN lower(Location) LIKE '%rustavi%' THEN 'Georgia'
        WHEN lower(Location) LIKE '%bat%umi%' THEN 'Georgia'
        
        -- Germany
        WHEN UPPER(Location) LIKE '%GERMANY%' THEN 'Germany'  
          WHEN UPPER(Location) LIKE '%BERLIN%' THEN 'Germany'
          WHEN UPPER(Location) LIKE '%BREMEN%' THEN 'Germany'
          WHEN UPPER(Location) LIKE '%HAMBURG%' THEN 'Germany'
          WHEN UPPER(Location) LIKE '%MUNICH%' THEN 'Germany'
          WHEN UPPER(Location) LIKE '%NORTH RHINE-WESTPHALIA%' THEN 'Germany'
          WHEN UPPER(Location) LIKE '%DEUTSCHLAND%' THEN 'Germany '
          WHEN UPPER(Location) LIKE '%SAUERLAND%' THEN 'Germany'
          WHEN lower(Location) LIKE lower('%Lower Saxony%') THEN 'Germany'
          WHEN lower(Location) LIKE lower('%Solingen%') THEN 'Germany'
          WHEN lower(Location) LIKE lower('%hopfen am see%') THEN 'Germany'
          WHEN lower(Location) LIKE lower('%Lederhosnland%') THEN 'Germany'
          
          
          
        WHEN UPPER(Location) LIKE '%GHANA%' THEN 'Ghana'
        WHEN UPPER(Location) LIKE '%ACCRA%' THEN 'Ghana'
        
        WHEN UPPER(Location) LIKE '%GREECE%' THEN 'Greece'
          WHEN UPPER(Location) LIKE '%METAXOURGIO%' THEN 'Greece'
          WHEN UPPER(Location) LIKE '%THESSALONIKI%' THEN 'Greece'

        WHEN UPPER(Location) LIKE '%GUATEMALA%' THEN 'Guatemala'
        WHEN UPPER(Location) LIKE '%GUYANA%' THEN 'Guyana'

        WHEN UPPER(Location) LIKE '%HAITI%' THEN 'Haiti'              
        WHEN UPPER(Location) LIKE '%HONDURAS%' THEN 'Honduras'

        WHEN UPPER(Location) LIKE '%HONG KONG%' THEN 'China - Hong Kong'
          WHEN UPPER(Location) LIKE '%HONGKONG%' THEN 'China - Hong Kong'
          WHEN Location LIKE '%HKSAR%' THEN 'China - Hong Kong'
          
        WHEN UPPER(Location) LIKE '%HUNGARY%' THEN 'Hungary'
          WHEN UPPER(Location) LIKE '%BUDAPEST%' THEN 'Hungary'

        WHEN UPPER(Location) LIKE '%ICELAND%' THEN 'Iceland'

        -- India                
        WHEN UPPER(Location) LIKE '%INDIANA%' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%INDIA%' THEN 'India'

          WHEN UPPER(Location) LIKE '%AHMEDABAD%' THEN 'India'
          WHEN UPPER(Location) LIKE '%AJMER%' THEN 'India'
          WHEN lower(Location) LIKE lower('%ASANSOL%') THEN 'India'
          
          WHEN UPPER(Location) LIKE '%BANGALORE%' THEN 'India'

          WHEN UPPER(Location) LIKE '%BARODA, MI%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%BARODA%' THEN 'India'

          WHEN UPPER(Location) LIKE '%BENGALURU%' THEN 'India'
          WHEN UPPER(Location) LIKE '%BHILWARA%' THEN 'India'
          WHEN UPPER(Location) LIKE '%BHOPAL%' THEN 'India'
          WHEN UPPER(Location) LIKE '%BHUBNESHWAR%' THEN 'India'
          
          WHEN lower(Location) LIKE lower('%Calcutta%') THEN 'India'
          

          WHEN UPPER(Location) LIKE '%CHANDIGARH%' THEN 'India'
          WHEN UPPER(Location) LIKE '%CHANDIGHAR%' THEN 'India'
          WHEN UPPER(Location) LIKE '%CHANDIGHARH%' THEN 'India'

          WHEN UPPER(Location) LIKE '%CHENNAI%' THEN 'India'
          WHEN lower(Location) LIKE lower('%Cochin%') THEN 'India'
          
          WHEN UPPER(Location) LIKE '%DEHRADUN%' THEN 'India'
          WHEN UPPER(Location) LIKE '%DELHI%' THEN 'India'
          WHEN UPPER(Location) LIKE '%DOMBIVLI%' THEN 'India'

          WHEN UPPER(Location) LIKE '%GANDHINAGAR%' THEN 'India'
          WHEN UPPER(Location) LIKE '%GURGAON%' THEN 'India'
          WHEN UPPER(Location) LIKE '%HYDERABAD%' THEN 'India'

          WHEN UPPER(Location) LIKE '%INDORE, WV%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%INDORE%' THEN 'India'

          WHEN UPPER(Location) LIKE '%RAJASTHAN%' THEN 'India'
          WHEN UPPER(Location) LIKE '%JAMKALYANPUR%' THEN 'India'
          WHEN UPPER(Location) LIKE '%KERALA%' THEN 'India'
          WHEN lower(Location) LIKE lower('%Kochi%') THEN 'India'
          

          WHEN UPPER(Location) LIKE '%LUDHIANA%' THEN 'India'

          WHEN UPPER(Location) LIKE '%MADURAI%' THEN 'India'
          WHEN UPPER(Location) LIKE '%MAHARASHTRA%' THEN 'India'
          WHEN UPPER(Location) LIKE '%MULUND%' THEN 'India'
          WHEN UPPER(Location) LIKE '%MUMBAI%' THEN 'India'

          WHEN UPPER(Location) LIKE '%NASHIK%' THEN 'India'
          WHEN lower(Location) LIKE lower('%Nilambur%') THEN 'India'
          
          
          WHEN UPPER(Location) LIKE '%NOIDA%' THEN 'India'

          WHEN UPPER(Location) LIKE '%PATNA%' THEN 'India'
          WHEN UPPER(Location) LIKE '%PONDICHERRY%' THEN 'India'
          
          WHEN lower(Location) LIKE lower('%Porbandar%') THEN 'India'
          
          
          WHEN UPPER(Location) LIKE '%PUNE%' THEN 'India'

          WHEN UPPER(Location) LIKE '%RAJKOT%' THEN 'India'
          
          WHEN lower(Location) LIKE lower('%Ranchi%') THEN 'India'
          WHEN lower(Location) LIKE lower('%Solapur%') THEN 'India'
          
          WHEN UPPER(Location) LIKE '%SURAT%' THEN 'India'
          WHEN UPPER(Location) LIKE '%TAMIL NADU%' THEN 'India'
          WHEN UPPER(Location) LIKE '%TRIVANDRUM%' THEN 'India'

          WHEN UPPER(Location) LIKE '%UDAIPUR%' THEN 'India'
          WHEN lower(Location) LIKE lower('%Uttarakhand%') THEN 'India'
          
          WHEN UPPER(Location) LIKE '%VIJAYAWADA%' THEN 'India'
          WHEN UPPER(Location) LIKE '%VISHAKAPATNAM%' THEN 'India'
          WHEN UPPER(Location) LIKE '%VIZAG%' THEN 'India'
                
        WHEN UPPER(Location) LIKE '%INDONESIA%' THEN 'Indonesia'
        
          WHEN lower(Location) LIKE '%ambon%' THEN 'Indonesia'
          
          WHEN lower(Location) LIKE '%balilihan%' THEN 'Philippines'
          WHEN lower(Location) LIKE '%balingen%' THEN 'Germany'
          
          WHEN lower(Location) LIKE '%bali%' THEN 'Indonesia'
          
          WHEN lower(Location) LIKE '%bandung%' THEN 'Indonesia'
          WHEN lower(Location) LIKE '%batam%' THEN 'Indonesia'
          WHEN lower(Location) LIKE '%bekasi%' THEN 'Indonesia'
          WHEN lower(Location) LIKE '%bogor%' THEN 'Indonesia'
               
          WHEN lower(Location) LIKE '%denpasar%' THEN 'Indonesia'
               
          WHEN lower(Location) LIKE '%depok%' THEN 'Indonesia'
          WHEN lower(Location) LIKE '%jakarta%' THEN 'Indonesia'
               
          WHEN lower(Location) LIKE '%malang%' THEN 'Indonesia'
          WHEN lower(Location) LIKE '%surabaya%' THEN 'Indonesia'
          WHEN lower(Location) LIKE '%yogyakarta%' THEN 'Indonesia'
          
        -- Iran
        WHEN Location LIKE '%IZMIR%' THEN 'Turkey'
        WHEN Location LIKE 'ADHAMI PIRAN JHANDUKHEL BANNU' THEN 'Pakistan'
        WHEN lower(Location) LIKE lower('%Altamirano%') THEN 'Mexico' -- Spam account
        WHEN lower(Location) LIKE lower('%Indiranagar%') THEN 'India'
      
        WHEN lower(Location) LIKE lower('%Mirandela%') THEN 'Portugal'
        WHEN lower(Location) LIKE lower('%Mirandola%') THEN 'Italy'
        WHEN lower(Location) LIKE lower('%Mirano%') THEN 'Italy'
      
        WHEN lower(Location) LIKE lower('%Pietravairano%') THEN 'Italy'
      
        WHEN lower(Location) LIKE lower('%Sapiranga%') THEN 'Brazil'
        WHEN lower(Location) LIKE lower('%Srirangam%') THEN 'India'
      
        WHEN lower(Location) LIKE lower('%Tirana%') THEN 'Albania'
        WHEN lower(Location) LIKE lower('%Tirane%') THEN 'Albania'
        WHEN lower(Location) LIKE lower('%Titirangi%') THEN 'New Zealand'
        
        WHEN UPPER(Location) LIKE '%IRAN%' THEN 'Iran'
          WHEN lower(Location) LIKE lower('%Babol%') THEN 'Iran'
          WHEN UPPER(Location) LIKE '%KASHAN%' THEN 'Iran'
          WHEN UPPER(Location) LIKE '%MASHHAD%' THEN 'Iran'
          WHEN UPPER(Location) LIKE '%MEHRSHAHR%' THEN 'Iran'
          WHEN UPPER(Location) LIKE '%TEHRAN%' THEN 'Iran'

        WHEN UPPER(Location) LIKE '%IRAQ%' THEN 'Iraq'
        WHEN UPPER(Location) LIKE '%ISRAEL%' THEN 'Israel'

        -- Italy
        WHEN UPPER(Location) LIKE '%ITALIA%' THEN 'Italy'
        WHEN UPPER(Location) LIKE '%ITALY%' THEN 'Italy'
          WHEN UPPER(Location) LIKE '%BOLOGNA%' THEN 'Italy'
          WHEN UPPER(Location) LIKE '%MILAN%' THEN 'Italy'
          WHEN UPPER(Location) LIKE '%NAPOLI%' THEN 'Italy'
          WHEN UPPER(Location) LIKE '%ROME%' THEN 'Italy'
          WHEN UPPER(Location) LIKE '%TRENTO%' THEN 'Italy'
          WHEN lower(Location) LIKE lower('%Isola del Giglio%') THEN 'Italy'
          

        WHEN UPPER(Location) LIKE '%JAMAICA%' THEN 'Jamaica'
        WHEN UPPER(Location) LIKE '%JAPAN%' THEN 'Japan'
          WHEN UPPER(Location) LIKE '%AIZUWAKAMATSU%' THEN 'Japan'
          

        WHEN UPPER(Location) LIKE '%JORDAN%' THEN 'Jordan'
          WHEN UPPER(Location) LIKE '%AMMAN%' THEN 'Jordan'

        WHEN UPPER(Location) LIKE '%KAZAKHSTAN%' THEN 'Kazakhstan'
        WHEN UPPER(Location) LIKE '%KENYA%' THEN 'Kenya'
        
        
        --Korea
        WHEN UPPER(Location) LIKE '%NORTH KOREA%' THEN 'North Korea'
          WHEN UPPER(Location) LIKE '%Pyongyang%' THEN 'North Korea'

        WHEN UPPER(Location) LIKE '%SOUTH KOREA%' THEN 'South Korea'
          WHEN UPPER(Location) LIKE '%SEOUL%' THEN 'South Korea'

        WHEN UPPER(Location) LIKE '%KOREA%' THEN 'South Korea'

        WHEN UPPER(Location) LIKE '%KUWAIT%' THEN 'Kuwait'
        WHEN UPPER(Location) LIKE '%KYRGYZSTAN%' THEN 'Kyrgyzstan'

        WHEN UPPER(Location) LIKE '%LATVIA%' THEN 'Latvia'
        WHEN UPPER(Location) LIKE '%LEBANON%' THEN 'Lebanon'
        WHEN UPPER(Location) LIKE '%LIBYA%' THEN 'Libya'
        WHEN UPPER(Location) LIKE '%LIECHTENSTEIN%' THEN 'Liechtenstein'
        WHEN UPPER(Location) LIKE '%LITHUANIA%' THEN 'Lithuania'
        WHEN UPPER(Location) LIKE '%LUXEMBOURG%' THEN 'Luxembourg'

        WHEN UPPER(Location) LIKE '%MACEDONIA%' THEN 'Macedonia'

        WHEN UPPER(Location) LIKE '%MALAYSIA%' THEN 'Malaysia'
          WHEN UPPER(Location) LIKE '%PENANG%' THEN 'Malaysia'

        WHEN UPPER(Location) LIKE '%MALDIVES%' THEN 'Maldives'
        WHEN UPPER(Location) LIKE '%MALTA%' THEN 'Malta'
        WHEN UPPER(Location) LIKE '%MAURITANIA%' THEN 'Mauritania'

        WHEN UPPER(Location) LIKE '%MAURITIUS%' THEN 'Mauritius'
        
        WHEN UPPER(Location) LIKE '%NEW MEXICO%' THEN 'United States'
        
        WHEN lower(Location) LIKE lower('%M%xico%') THEN 'Mexico - M%xico'
          WHEN UPPER(Location) LIKE '%MEXICALI%' THEN 'Mexico'
          WHEN UPPER(Location) LIKE '%MEX%' THEN 'Mexico'
          
        WHEN UPPER(Location) LIKE '%MOLDOVA%' THEN 'Moldova'
        WHEN UPPER(Location) LIKE '%MONGOLIA%' THEN 'Mongolia'
        WHEN UPPER(Location) LIKE '%MONTENEGRO%' THEN 'Montenegro'

        WHEN UPPER(Location) LIKE '%MOROCCO%' THEN 'Morocco'
          WHEN UPPER(Location) LIKE '%TIZNIT%' THEN 'Morocco'

        WHEN UPPER(Location) LIKE '%MOZAMBIQUE%' THEN 'Mozambique'
        WHEN UPPER(Location) LIKE '%MYANMAR%' THEN 'Myanmar'

        WHEN UPPER(Location) LIKE '%NAMIBIA%' THEN 'Namibia'
        
        WHEN UPPER(Location) LIKE '%NEPAL%' THEN 'Nepal'
          WHEN UPPER(Location) LIKE '%KATHMANDU%' THEN 'Nepal'

        -- Netherlands
        WHEN UPPER(Location) LIKE '%NETHERLANDS%' THEN 'Netherlands'
          WHEN UPPER(Location) LIKE '%AMSTERDAM%' THEN 'Netherlands'
          WHEN UPPER(Location) LIKE '%ARUBA%' THEN 'Netherlands'

        WHEN UPPER(Location) LIKE '%NEW CALEDONIA%' THEN 'New Caledonia'

        -- New Zealand
        WHEN UPPER(Location) LIKE '%NEW ZEALAND%' THEN 'New Zealand'
          WHEN UPPER(Location) LIKE '%AUCKLAND%' THEN 'New Zealand'
          WHEN UPPER(Location) LIKE '%CHRISTCHURCH%' THEN 'New Zealand'
          WHEN lower(Location) LIKE lower('%Titirangi%')  THEN 'New Zealand'
          
          --WHEN UPPER(Location) LIKE '%HAMILTON, NEW Z%' THEN 'New Zealand'
            --WHEN UPPER(Location) LIKE '%HAMILTON,NEW Z%' THEN 'New Zealand'
            --WHEN UPPER(Location) LIKE '%HAMILTON NEW Z%' THEN 'New Zealand'
          
          
        WHEN UPPER(Location) LIKE '%NICARAGUA%' THEN 'Nicaragua'
        
        -- Nigeria must precede Niger
        WHEN UPPER(Location) LIKE '%NIGERIA%' THEN 'Nigeria'
        WHEN UPPER(Location) LIKE '%NIGER%' THEN 'Niger'

        WHEN UPPER(Location) LIKE '%NORWAY%' THEN 'Norway'
          WHEN UPPER(Location) LIKE '%NOR' THEN 'Norway'
          WHEN UPPER(Location) LIKE '%SVALBARD%' THEN 'Norway'

        WHEN UPPER(Location) LIKE '%OMAN%' THEN 'Oman'
        
        WHEN UPPER(Location) LIKE '%PAKISTAN%' THEN 'Pakistan'
          WHEN UPPER(Location) LIKE '%KARACHI%' THEN 'Pakistan'
          WHEN UPPER(Location) LIKE '%LAHORE%' THEN 'Pakistan'

        WHEN UPPER(Location) LIKE '%PALESTINE, AR%' THEN 'United States'
        WHEN UPPER(Location) LIKE '%PALESTINE%' THEN 'Palestine'
          WHEN UPPER(Location) LIKE '%PALESTIN%' THEN 'Palestine'

        WHEN UPPER(Location) LIKE '%PANAMA%' THEN 'Panama'
        WHEN UPPER(Location) LIKE '%PARAGUAY%' THEN 'Paraguay'
        WHEN UPPER(Location) LIKE '%PERU%' THEN 'Peru'

        WHEN UPPER(Location) LIKE '%PHILIPPINES%' THEN 'Philippines'
          WHEN UPPER(Location) LIKE '%MAKATI CITY%' THEN 'Philippines'

        WHEN UPPER(Location) LIKE '%POLAND%' THEN 'Poland'
          WHEN UPPER(Location) LIKE '%KRAKOW%' THEN 'Poland'
          WHEN UPPER(Location) LIKE '%WARSAW%' THEN 'Poland'

        -- PORTUGAL
        WHEN UPPER(Location) LIKE '%PORTUGAL%' THEN 'Portugal'
          WHEN UPPER(Location) LIKE '%LISBON%' THEN 'Portugal'

        WHEN UPPER(Location) LIKE '%PUERTO RICO%' THEN 'Puerto Rico'
        WHEN UPPER(Location) LIKE '%QATAR%' THEN 'Qatar'
        
        WHEN UPPER(Location) LIKE '%ROMANIA%' THEN 'Romania'
        WHEN Location LIKE '%România%' THEN 'Romania'
        WHEN Location LIKE '%românia%' THEN 'Romania'
        

        WHEN UPPER(Location) LIKE '%RUSSIA%' THEN 'Russia'
          WHEN UPPER(Location) LIKE '%KRASNOYARSK%' THEN 'Russia'
          WHEN UPPER(Location) LIKE '%MOSCOW%' THEN 'Russia'
          WHEN UPPER(Location) LIKE '%SAINT-PETERSBURG%' THEN 'Russia'
          WHEN UPPER(Location) LIKE '%SIBERIA%' THEN 'Russia'
          WHEN UPPER(Location) LIKE '%USSIA%' THEN 'Russia' -- %USSIA with flipped R'
          
          
        -- SAMOA
        WHEN UPPER(Location) LIKE '%AMERICAN SAMOA%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%SAMOA%' THEN 'Samoa'

        WHEN UPPER(Location) LIKE '%SAN MARINO%' THEN 'San Marino'
        WHEN UPPER(Location) LIKE '%SAUDI ARABIA%' THEN 'Saudi Arabia'
          WHEN Location LIKE '%KSA' THEN 'Saudi Arabia' --, Kingdom of'
        
        WHEN UPPER(Location) LIKE '%SENEGAL%' THEN 'Senegal'
        WHEN UPPER(Location) LIKE '%SERBIA%' THEN 'Serbia'
        WHEN UPPER(Location) LIKE '%SEYCHELLES%' THEN 'Seychelles'

        WHEN UPPER(Location) LIKE '%SIERRA LEONE%' THEN 'Sierra Leone'
        WHEN UPPER(Location) LIKE '%SINGAPORE%' THEN 'Singapore'
        WHEN UPPER(Location) LIKE '%SLOVAKIA%' THEN 'Slovakia'
        WHEN UPPER(Location) LIKE '%SLOVENIA%' THEN 'Slovenia'
        
        WHEN UPPER(Location) LIKE '%SOUTH AFRICA%' THEN 'South Africa'
          WHEN UPPER(Location) LIKE '%SANDTON%' THEN 'South Africa'
          WHEN Location LIKE '%RSA' THEN 'South Africa' --, Republic of'
       
        -- Spain
        WHEN UPPER(Location) LIKE '%SPAIN%' THEN 'Spain'
        WHEN lower(Location) LIKE lower('%España%') THEN 'Spain'
          WHEN UPPER(Location) LIKE '%ALICANTE%' THEN 'Spain'
          WHEN UPPER(Location) LIKE '%BARCELONA%' THEN 'Spain'
          WHEN UPPER(Location) LIKE '%CANARIA%' THEN 'Spain'
          WHEN UPPER(Location) LIKE '%CANARY%' THEN 'Spain'
          WHEN UPPER(Location) LIKE '%CATALONIA%' THEN 'Spain'
          WHEN lower(Location) LIKE lower('%Madrid%') THEN 'Spain'
          WHEN UPPER(Location) LIKE '%VALENCIA%' THEN 'Spain'

        WHEN UPPER(Location) LIKE '%SRI LANKA%' THEN 'Sri Lanka'
          WHEN UPPER(Location) LIKE '%BATTARAMULLA%' THEN 'Sri Lanka'
          WHEN lower(Location) LIKE lower('%Bandarawela%') THEN 'Sri Lanka'
          

        WHEN UPPER(Location) LIKE '%SUDAN%' THEN 'Sudan'
        WHEN UPPER(Location) LIKE '%SWAZILAND%' THEN 'Swaziland'
                
        WHEN UPPER(Location) LIKE '%SWEDEN%' THEN 'Sweden'
          WHEN lower(Location) LIKE lower('%Sollentuna%') THEN 'Sweden'
          WHEN lower(Location) LIKE lower('%Solna%') THEN 'Sweden'

        WHEN UPPER(Location) LIKE '%SWITZERLAND%' THEN 'Switzerland'
          WHEN UPPER(Location) LIKE '%SWIZERLAND%' THEN 'Switzerland'

        WHEN UPPER(Location) LIKE '%SYRIA%' THEN 'Syria'

        WHEN UPPER(Location) LIKE '%TAIWAN%' THEN 'Taiwan'
        WHEN UPPER(Location) LIKE '%TAJIKISTAN%' THEN 'Tajikistan'
        WHEN UPPER(Location) LIKE '%TANZANIA%' THEN 'Tanzania'
        
        WHEN UPPER(Location) LIKE '%THAILAND%' THEN 'Thailand'
          WHEN UPPER(Location) LIKE '%CHONBURI%' THEN 'Thailand'
        
        
        WHEN lower(Location) LIKE lower('%Tonga%') THEN 'Tonga'

        WHEN UPPER(Location) LIKE '%TOGO%' THEN 'Togo'
        
        WHEN UPPER(Location) LIKE '%TRINIDAD AND%' THEN 'Trinidad and Tobago'
          WHEN UPPER(Location) LIKE '%TOBAGO%' THEN 'Trinidad and Tobago'
        
        WHEN UPPER(Location) LIKE '%TUNISIA%' THEN 'Tunisia'
        WHEN lower(Location) LIKE lower('TUNISA') THEN 'Tunisia - typo'

        WHEN UPPER(Location) LIKE '%TURKEY%' THEN 'Turkey'
          WHEN UPPER(Location) LIKE '%TURKIYE%' THEN 'Turkey'
         
          WHEN UPPER(Location) LIKE '%ADANA%' THEN 'Turkey'
          WHEN UPPER(Location) LIKE '%ANKARA%' THEN 'Turkey'
          WHEN UPPER(Location) LIKE '%BURSA%' THEN 'Turkey'
          WHEN UPPER(Location) LIKE '%ISTANBUL%' THEN 'Turkey'
                
        WHEN UPPER(Location) LIKE '%TURKMENISTAN%' THEN 'Turkmenistan'
 
        WHEN UPPER(Location) LIKE '%UGANDA%' THEN 'Uganda'
          WHEN UPPER(Location) LIKE '%KAMPALA%' THEN 'Uganda'

        --WHEN lower(Location) LIKE lower('%Odessa%') THEN 'Ukraine odessa'
        WHEN UPPER(Location) LIKE '%UKRAINE%' THEN 'Ukraine'
          WHEN UPPER(Location) LIKE '%KHARKOV%' THEN 'Ukraine'
          WHEN UPPER(Location) LIKE '%KIEV%' THEN 'Ukraine'
          WHEN UPPER(Location) LIKE '%KYIV%' THEN 'Ukraine'
          WHEN lower(Location) LIKE lower('%Odessa%') THEN 'Ukraine odessa'

        WHEN UPPER(Location) LIKE '%UNITED ARAB EMIRATES%' THEN 'United Arab Emirates'
          WHEN UPPER(Location) LIKE '%DUBAI%' THEN 'United Arab Emirates'
                
        -- United Kingdom & Ireland
        WHEN UPPER(Location) LIKE '%GIBRALTAR%' THEN 'United Kingdom'
        WHEN UPPER(Location) LIKE '%ISLE OF MAN%' THEN 'United Kingdom'
        WHEN UPPER(Location) LIKE '%TUVALU%' THEN 'United Kingdom'

        WHEN UPPER(Location) LIKE '%NEW ENGLAND%' THEN 'United States'
        WHEN UPPER(Location) LIKE '%ENGLAND, AR%' THEN 'United States'
        WHEN UPPER(Location) LIKE '%ENGLAND%' THEN 'United Kingdom'
                
        WHEN UPPER(Location) LIKE '%NORTHERN IRELAND%' THEN 'United Kingdom'
        WHEN UPPER(Location) LIKE '%SCOTLAND%' THEN 'United Kingdom'
        WHEN UPPER(Location) LIKE '%WALES%' THEN 'United Kingdom'
        
        WHEN UPPER(Location) LIKE '%UNITED KINGDOM%' THEN 'United Kingdom'
        WHEN Location LIKE '%, UK%' THEN 'United Kingdom'
        WHEN Location LIKE '%,UK %' THEN 'United Kingdom'
        WHEN Location LIKE '%UK' THEN 'United Kingdom'
        
        WHEN lower(location) like '%, u.k.%' THEN 'United Kingdom'
        WHEN lower(location) like '%,u.k. %' THEN 'United Kingdom'
        WHEN lower(location) like '%u.k.'    THEN 'United Kingdom'
     
        WHEN lower(location) like '%, u.k%' THEN 'United Kingdom'
        WHEN lower(location) like '%,u.k %' THEN 'United Kingdom'
        WHEN lower(location) like '%u.k'    THEN 'United Kingdom'
     
        WHEN lower(location) like '%, uk%' THEN 'United Kingdom'
        WHEN lower(location) like '%,uk %' THEN 'United Kingdom'
        WHEN lower(location) like '%uk'    THEN 'United Kingdom'
        
        WHEN location like '%UK,%'         THEN 'United Kingdom'
        
        WHEN lower(location) LIKE lower('%Great Britain%') THEN 'United Kingdom'
        WHEN lower(Location) LIKE '%, g.b.%' THEN 'United Kingdom'
        WHEN lower(Location) LIKE '%,g.b. %' THEN 'United Kingdom'
        WHEN lower(Location) LIKE '%g.b.'    THEN 'United Kingdom'
        
        WHEN lower(Location) LIKE '%, g.b%'  THEN 'United Kingdom'
        WHEN lower(Location) LIKE '%,g.b %'  THEN 'United Kingdom'
        WHEN lower(Location) LIKE '%g.b'     THEN 'United Kingdom'
     
        WHEN lower(Location) LIKE '%, gb%'   THEN 'United Kingdom'
        WHEN lower(Location) LIKE '%,gb %'   THEN 'United Kingdom'
        WHEN lower(Location) LIKE '%gb'      THEN 'United Kingdom'
        
        WHEN lower(Location) LIKE lower('%Bedfordshire%') THEN 'United Kingdom'

        WHEN UPPER(Location) LIKE '%BRIGHTON, AL%' THEN 'United States'
        WHEN lower(Location) LIKE lower('%Brighton Beach, NYC%') THEN 'United States'
        WHEN lower(Location) LIKE lower('%Brighton, Michigan%') THEN 'United States'
        WHEN lower(Location) LIKE lower('%New Brighton, MN%') THEN 'United States'
        
        -- Assumed Brighton UK, after New Zealand
        WHEN UPPER(Location) LIKE '%BRIGHTON%' THEN 'United Kingdom'
        WHEN UPPER(Location) LIKE '%EDINBURGH%' THEN 'United Kingdom' 
        
        WHEN lower(Location) = lower('London') THEN 'United Kingdom' -- assumed       
        -- Must be after Canada / Ontario
        WHEN lower(Location) LIKE lower('%London%') THEN 'United Kingdom' -- assumed'

        WHEN UPPER(Location) LIKE '%MANCHESTER%' THEN 'United Kingdom'
        WHEN UPPER(Location) LIKE '%NEWCASTLE%' THEN 'United Kingdom'
        WHEN UPPER(Location) LIKE '%NOTTINGHAM%' THEN 'United Kingdom'              

        -- Must be after Canada, Peterborough, Ontario
        WHEN UPPER(Location) LIKE '%PETERBOROUGH%' THEN 'United Kingdom'
        
        WHEN UPPER(Location) LIKE '%SWANSEA%' THEN 'United Kingdom'
        WHEN UPPER(Location) LIKE '%WARRINGTON%' THEN 'United Kingdom'

        WHEN UPPER(Location) LIKE '%IRELAND%' THEN 'Ireland, Republic of'
          WHEN UPPER(Location) LIKE '%DUBLIN%' THEN 'Ireland, Republic of'

        -- United States
        
        -- Note the two letter abbreviations have to be near the end of the entire search

        WHEN UPPER(Location) LIKE '%UNITED STATES%' THEN 'United States'
        WHEN lower(Location) LIKE lower('%United Sates%') THEN 'United States'  -- Typo United Sates'
        WHEN UPPER(Location) LIKE 'USA' THEN 'United States'
        WHEN UPPER(Location) LIKE '%US OF A%' THEN 'United States'
        
        WHEN lower(Location) LIKE lower('AMURICA') THEN 'United States'
        WHEN lower(Location) LIKE lower('America') THEN 'United States'
        
        -- Should not find any. This is before England
        WHEN UPPER(Location) LIKE '%NEW ENGLAND%' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%US VIRGIN ISLANDS%' THEN 'United States'
        WHEN UPPER(Location) LIKE '%VIRGIN ISLANDS%' THEN 'United Kingdom'
 
        WHEN UPPER(Location) LIKE '%ALLAHABAD%' THEN 'India'
        
        WHEN UPPER(Location) LIKE '%ALABAMA%' THEN 'United States'
          WHEN Location LIKE '%, AL%' THEN 'United States'
          WHEN Location LIKE '%,AL %' THEN 'United States'
          WHEN Location LIKE '%AL' THEN 'United States' --bhopAL, senegAL, typo - ISREAL
          WHEN Location LIKE '%AL.' THEN 'United States' -- special case
        
        WHEN UPPER(Location) LIKE '%ALASKA%' THEN 'United States'
          WHEN Location LIKE '%, AK%' THEN 'United States'
          WHEN Location LIKE '%,AK %' THEN 'United States'
          WHEN Location LIKE '%AK' THEN 'United States'
          
        WHEN UPPER(Location) LIKE '%ARIZONA%' THEN 'United States'
          WHEN Location LIKE '%, AZ%' THEN 'United States'
          WHEN Location LIKE '%,AZ %' THEN 'United States'
          WHEN Location LIKE '%AZ' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%ARKANSAS%' THEN 'United States'
          WHEN Location LIKE '%, AR%' THEN 'United States'
          WHEN Location LIKE '%,AR %' THEN 'United States'
          WHEN Location LIKE '%AR' THEN 'United States'
          
        WHEN UPPER(Location) LIKE '%CALIFORNIA%' THEN 'United States'
        WHEN UPPER(Location) LIKE '%VENICE,CA%' THEN 'United States'
          -- Must follow CANADA
          WHEN Location LIKE '%Hamilton, Ca%' THEN 'United States'
          WHEN Location LIKE '%, CA%' THEN 'United States' -- unlikely to be Canada
          WHEN Location LIKE '%,CA %' THEN 'United States' -- unlikely to be Canada
          WHEN Location LIKE '%CA' THEN 'United States' -- unlikely to be Canada
          WHEN lower(Location) LIKE lower('%S.F%Bay%') THEN 'United States'
          

        WHEN UPPER(Location) LIKE '%COLORADO%' THEN 'United States'
          -- Must follow COLOMBIA, MOROCCO, MEXICO, NEW MEXICO 
          WHEN Location LIKE '%, CO%' THEN 'United States'
          WHEN Location LIKE '%,CO %' THEN 'United States'
          WHEN Location LIKE '%CO' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%CONNECTICUT%' THEN 'United States'
          WHEN Location LIKE '%, CT%' THEN 'United States'
          WHEN Location LIKE '%,CT %' THEN 'United States'
          WHEN Location LIKE '%CT' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%DELAWARE%' THEN 'United States'
          WHEN Location LIKE 'Your favorite IDE' THEN 'Unknown - Seen'
          WHEN Location LIKE '%, DE%' THEN 'United States'
          WHEN Location LIKE '%,DE %' THEN 'United States'
          WHEN Location LIKE '%DE' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%DISTRICT OF COLUMBIA%' THEN 'United States'
          WHEN Location LIKE '%, DC%' THEN 'United States'
          WHEN Location LIKE '%,DC %' THEN 'United States'
          WHEN Location LIKE '%DC' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%FLORIDA%' THEN 'United States'
          WHEN Location LIKE '%, FL%' THEN 'United States'
          WHEN Location LIKE '%,FL %' THEN 'United States'
          WHEN Location LIKE '%FL' THEN 'United States'

        -- US Georgia
        WHEN UPPER(Location) LIKE '%TBILISI%' THEN 'Georgia'
        WHEN UPPER(Location) LIKE '%KHELVACHAURI%' THEN 'Georgia'
        
        WHEN UPPER(Location) LIKE '%ATHENS, GEORGIA%' THEN 'United States'
        WHEN UPPER(Location) LIKE '%ATLANTA, GEORGIA%' THEN 'United States'

        WHEN UPPER(Location) LIKE 'GEORGIA' THEN 'United States' -- or Georgia'
        WHEN UPPER(Location) LIKE '%GEORGIA%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, GA%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,GA %' THEN 'United States'
          WHEN UPPER(Location) LIKE '%GA' THEN 'United States'

        WHEN UPPER(Location) LIKE '%HAWAII%' THEN 'United States'
          WHEN Location LIKE '%, HI%' THEN 'United States'
          WHEN Location LIKE '%,HI %' THEN 'United States'
          WHEN Location LIKE '%HI' THEN 'United States'

        WHEN UPPER(Location) LIKE '%IDAHO%' THEN 'United States'
          WHEN Location LIKE '%, ID%' THEN 'United States'
          WHEN Location LIKE '%,ID %' THEN 'United States'
          WHEN Location LIKE '%ID' THEN 'United States'
          WHEN lower(Location) LIKE lower('%Caldwell%') THEN 'United States'
          
        
        WHEN UPPER(Location) LIKE '%ILLINOIS%' THEN 'United States'
          WHEN Location LIKE '%, IL%' THEN 'United States'
          WHEN Location LIKE '%,IL %' THEN 'United States'
          WHEN Location LIKE '%IL' THEN 'United States'
                
        -- Should not find any. This is the first line for India
        WHEN UPPER(Location) LIKE '%INDIANA%' THEN 'United States'
          WHEN Location LIKE '%, IN%' THEN 'United States'
          WHEN Location LIKE '%,IN %' THEN 'United States'
          WHEN Location LIKE 'SIN' THEN 'Unknown - Seen'
          WHEN Location LIKE '%IN' THEN 'United States'
                
        WHEN UPPER(Location) LIKE '%IOWA%' THEN 'United States'
          WHEN Location LIKE '%, IA%' THEN 'United States'
          WHEN Location LIKE '%,IA %' THEN 'United States'
          
          WHEN Location LIKE '%ASIA' THEN 'Asia'
          WHEN Location LIKE '%IA' THEN 'United States'

        WHEN UPPER(Location) LIKE '%KANSAS%' THEN 'United States'
          WHEN Location LIKE '%, KSA%' THEN 'Saudi Arabia'
          WHEN Location LIKE '%, KS%' THEN 'United States'
          WHEN Location LIKE '%,KS %' THEN 'United States'
          WHEN Location LIKE '%KS' THEN 'United States'

        WHEN UPPER(Location) LIKE '%KENTUCKY%' THEN 'United States'
          WHEN Location LIKE '%, KY%' THEN 'United States'
          WHEN Location LIKE '%,KY %' THEN 'United States'
          WHEN Location LIKE '%KY' THEN 'United States'

        WHEN UPPER(Location) LIKE '%LOUISIANA%' THEN 'United States'
          WHEN Location LIKE '%, LA%' THEN 'United States'
          WHEN Location LIKE '%,LA %' THEN 'United States'
          WHEN Location LIKE '%LA' THEN 'United States'

        WHEN UPPER(Location) LIKE '%MAINE%' THEN 'United States'
          WHEN Location LIKE '%, ME%' THEN 'United States'
          WHEN Location LIKE '%,ME %' THEN 'United States'
          WHEN Location LIKE '%ME' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%MARYLAND%' THEN 'United States'
          WHEN Location LIKE '%, MD%' THEN 'United States'
          WHEN Location LIKE '%,MD %' THEN 'United States'
          WHEN Location LIKE '%MD' THEN 'United States'

        WHEN UPPER(Location) LIKE '%MASSACHUSETTS%' THEN 'United States'
          -- Must be after Malaysia, Maine
          WHEN Location LIKE '%, MA%' THEN 'United States'
          WHEN Location LIKE '%,MA %' THEN 'United States'
          WHEN Location LIKE '%MA' THEN 'United States'
          WHEN lower(Location) like lower('New Bedford') THEN 'United States'
          
        
        WHEN UPPER(Location) LIKE '%MICHIGAN%' THEN 'United States'
          WHEN Location = 'Planet Earth, Solar System, MIlky Way.' THEN 'Offbeat - Earth'
          WHEN Location LIKE '%, MI%' THEN 'United States'
          WHEN Location LIKE '%,MI %' THEN 'United States'
          WHEN Location LIKE '%MI' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%MINNESOTA%' THEN 'United States'
          WHEN Location LIKE '%, MN%' THEN 'United States'
          WHEN Location LIKE '%,MN %' THEN 'United States'
          WHEN Location LIKE '%MN' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%MISSISSIPPI%' THEN 'United States'
          WHEN Location LIKE '%, MS%' THEN 'United States'
          WHEN Location LIKE '%,MS %' THEN 'United States'
          
          WHEN Location LIKE 'Best CMS' THEN 'Offbeat'
          WHEN Location LIKE '%MS' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%MISSOURI%' THEN 'United States'
          WHEN Location LIKE '%, MO%' THEN 'United States'
          WHEN Location LIKE '%,MO %' THEN 'United States'
          WHEN Location LIKE '%MO' THEN 'United States'

        WHEN UPPER(Location) LIKE '%MONTANA%' THEN 'United States'
          WHEN Location LIKE '%, MT%' THEN 'United States'
          WHEN Location LIKE '%,MT%' THEN 'United States'
          WHEN Location LIKE 'GMT' THEN 'Offbeat'
          WHEN Location LIKE '%MT' THEN 'United States'

        WHEN UPPER(Location) LIKE '%NEBRASKA%' THEN 'United States'
          WHEN Location LIKE '%, NE%' THEN 'United States'
          WHEN Location LIKE '%,NE %' THEN 'United States'
          WHEN Location LIKE '%NE' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%NEVADA%' THEN 'United States'
          WHEN Location LIKE '%, NV%' THEN 'United States'
          WHEN Location LIKE '%,NV %' THEN 'United States'
          WHEN Location LIKE '%NV' THEN 'United States'
          
        WHEN UPPER(Location) LIKE '%NEW HAMPSHIRE%' THEN 'United States'
          WHEN Location LIKE '%, NH%' THEN 'United States'
          WHEN Location LIKE '%,NH %' THEN 'United States'
          WHEN Location LIKE '%NH' THEN 'United States'
  
        WHEN UPPER (Location) LIKE '%NEW JERSEY%' THEN 'United States'
          WHEN Location LIKE '%, NJ%' THEN 'United States'
          WHEN Location LIKE '%,NJ%' THEN 'United States'
          
          WHEN Location LIKE 'DE SNJ' THEN 'Offbeat'
          WHEN Location LIKE '%NJ' THEN 'United States'

        -- Should not find any. This is the first line for Mexico
        WHEN UPPER(Location) LIKE '%NEW MEXICO%' THEN 'United States'
          
          WHEN Location LIKE 'NYC, NMY' THEN 'United States'
          WHEN Location LIKE '%, NM%' THEN 'United States'
          WHEN Location LIKE '%,NM %' THEN 'United States'
          WHEN Location LIKE '%NM' THEN 'United States'

        -- New York State
        WHEN UPPER(Location) LIKE '%NEW YORK%' THEN 'United States'
          WHEN Location LIKE '%, NY%' THEN 'United States'
          WHEN Location LIKE '%,NY%' THEN 'United States'
          WHEN Location LIKE '%NY' THEN 'United States' -- Includes WNY - Western New York
          
          --WHEN UPPER(Location) LIKE '%HAMILTON, NEW Y%' THEN 'United States'
            --WHEN UPPER(Location) LIKE '%HAMILTON,NEW Y%' THEN 'United States'
            --WHEN UPPER(Location) LIKE '%HAMILTON NEW Y%' THEN 'United States'
            
            --WHEN UPPER(Location) LIKE '%HAMILTON, NY%' THEN 'United States'
            --WHEN UPPER(Location) LIKE '%HAMILTON,NY%' THEN 'United States'
            --WHEN UPPER(Location) LIKE '%HAMILTON NY%' THEN 'United States'
          
          WHEN UPPER(Location) LIKE '%NYC%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%LONG ISLAND%' THEN 'United States'
          
        WHEN UPPER(Location) LIKE '%NORTH CAROLINA%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, NC%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,NC %' THEN 'United States'
          
          WHEN UPPER(Location) LIKE 'TRNC' THEN 'Cyprus' -- Turkish Republic of Northern Cyprus
          WHEN UPPER(Location) LIKE '%NC' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%NORTH DAKOTA%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, ND%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,ND %' THEN 'United States'
          
          WHEN Location LIKE '50M3WH3R3 4R0UND' THEN 'Offbeat - Somewhere'
          WHEN UPPER(Location) LIKE '%ND' THEN 'United States'

        WHEN UPPER(Location) LIKE '%OHIO%' THEN 'United States'
          WHEN Location LIKE '%, OH%' THEN 'United States'
          WHEN Location LIKE '%,OH %' THEN 'United States'
          WHEN Location LIKE '%OH' THEN 'United States'
        
        WHEN lower(Location) LIKE lower('%Okinawa%') THEN 'Japan'
        WHEN UPPER(Location) LIKE '%OKLAHOMA%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, OK%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,OK %' THEN 'United States'
          WHEN UPPER(Location) LIKE '%OK' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%OREGON%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, OR%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,OR %' THEN 'United States'
          WHEN UPPER(Location) LIKE '%OR' THEN 'United States' --NOR is Norway
          
        WHEN UPPER(Location) LIKE '%PENNSYLVANIA%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, PA%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,PA %' THEN 'United States'
          WHEN UPPER(Location) LIKE '%PA' THEN 'United States'

        WHEN UPPER(Location) LIKE '%RHODE ISLAND%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, RI%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,RI %' THEN 'United States'
          
          WHEN UPPER(Location) LIKE 'IRI' THEN 'Unknown - Seen'
          WHEN UPPER(Location) LIKE '%RI' THEN 'United States'
          
        WHEN UPPER(Location) LIKE '%SOUTH CAROLINA%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, SC%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,SC %' THEN 'United States'
          
          WHEN Location LIKE 'Milky Way, LSC' THEN 'Offbeat - Space'
          WHEN Location LIKE 'PRC.SC' THEN 'Unknown - Seen'
          WHEN UPPER(Location) LIKE '%SC' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%SOUTH DAKOTA%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, SD%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,SD %' THEN 'United States'
          WHEN UPPER(Location) LIKE '%SD' THEN 'United States'

        WHEN UPPER(Location) LIKE '%TENNESSEE%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, TN%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,TN %' THEN 'United States'
          WHEN UPPER(Location) LIKE '%TN' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%TEXAS%' THEN 'United States'
          -- Includes Earth, TX
          WHEN UPPER(Location) LIKE '%, TX%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,TX %' THEN 'United States'
          WHEN UPPER(Location) LIKE '%TX' THEN 'United States' -- ATX - Austin / DFW TX / HTX / SATX
          WHEN lower(Location) LIKE lower('%Austin%') THEN 'United States'
         
        WHEN UPPER(Location) LIKE '%UTAH%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, UT%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,UT %' THEN 'United States'
          WHEN UPPER(Location) LIKE '%UT' THEN 'United States'

        WHEN UPPER(Location) LIKE '%VERMONT%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%, VT%' THEN 'United States'
          WHEN UPPER(Location) LIKE '%,VT %' THEN 'United States'
          WHEN UPPER(Location) LIKE '%VT' THEN 'United States'


        WHEN UPPER(Location) LIKE '%VIRGINIA%' THEN 'United States'
          WHEN Location LIKE '%, VA%' THEN 'United States'
          WHEN Location LIKE '%,VA %' THEN 'United States'
          WHEN Location LIKE '%VA' THEN 'United States'

        WHEN UPPER(Location) LIKE '%WASHINGTON%' THEN 'United States'
          WHEN Location LIKE '%, WA%' THEN 'United States'
          WHEN Location LIKE '%,WA %' THEN 'United States'
          WHEN Location LIKE '%WA' THEN 'United States'
          
        WHEN UPPER(Location) LIKE '%WEST VIRGINIA%' THEN 'United States' --covered by VIRGINIA
          WHEN Location LIKE '%, WV%' THEN 'United States'
          WHEN Location LIKE '%,WV %' THEN 'United States'
          WHEN Location LIKE '%WV' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%WISCONSIN%' THEN 'United States'
          WHEN Location LIKE '%, WI%' THEN 'United States'
          WHEN Location LIKE '%,WI %' THEN 'United States'
          WHEN Location LIKE '%WI' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%WYOMING%' THEN 'United States'
          WHEN Location LIKE '%, WY%' THEN 'United States'
          WHEN Location LIKE '%,WY %' THEN 'United States'
          WHEN Location LIKE '%WY' THEN 'United States'

        -- US cities
        WHEN UPPER(Location) LIKE '%BOSTON%' THEN 'United States'
        WHEN UPPER(Location) LIKE '%CHICAGO%' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%HOLLYWOOD%' THEN 'United States'
        WHEN UPPER(Location) LIKE '%LOS ANGELES%' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%PITTSBURGH%' THEN 'United States'
        
        WHEN UPPER(Location) LIKE '%SALT LAKE CITY%' THEN 'United States'
        WHEN UPPER(Location) LIKE '%SAN FRAN%'       THEN 'United States'        
                  
        -- US Misc
        WHEN UPPER(Location) LIKE '%QUAD CITIES%'    THEN 'United States'
        WHEN UPPER(Location) LIKE '%ROCKY MOUNTAINS%'THEN 'United States'
        WHEN UPPER(Location) LIKE '%SILICON VALLEY%' THEN 'United States'
        WHEN lower(Location) LIKE lower('%Nor%Cal%') THEN 'United States' -- Northern California'
        WHEN lower(Location) LIKE lower('%So%Cal%')  THEN 'United States' -- Southern California'
        
        
        WHEN UPPER(Location) LIKE '%URUGUAY%' THEN 'Uruguay'
        WHEN UPPER(Location) LIKE '%USSR%' THEN 'USSR'
        WHEN UPPER(Location) LIKE '%UZBEKISTAN%' THEN 'Uzbekistan'
        WHEN UPPER(Location) LIKE '%VENEZUELA%' THEN 'Venezuela'
              
        -- Vietnam
        WHEN UPPER(Location) LIKE '%VIETNAM%' THEN 'Vietnam'
          WHEN UPPER(Location) LIKE '%VIET NAM%' THEN 'Vietnam'
          WHEN UPPER(Location) LIKE '%HO CHI MINH CITY%' THEN 'Vietnam'

        WHEN UPPER(Location) LIKE '%YEMEN%' THEN 'Yemen'
        WHEN UPPER(Location) LIKE '%ZAMBIA%' THEN 'Zambia'
        WHEN UPPER(Location) LIKE '%ZIMBABWE%' THEN 'Zimbabwe'
 
        WHEN UPPER(Location) LIKE '%NORTH AMERICA%' THEN 'North America'
        WHEN UPPER(Location) LIKE '%SOUTH AMERICA%' THEN 'South America'
        WHEN lower(Location) LIKE lower('%Central America%') THEN 'Central America'
        WHEN lower(Location) LIKE lower('%Latin America%') THEN 'Latin America'
        
        WHEN UPPER(Location) LIKE '%ASIA%' THEN 'Asia'
        WHEN UPPER(Location) LIKE '%EUROPE%' THEN 'Europe'
        WHEN UPPER(Location) LIKE '%AFRICA%' THEN 'Africa'
        
        WHEN lower(Location) LIKE lower('%America%') THEN 'United States' -- fallthrough America'
        
        WHEN lower(Location) LIKE lower('%Oceania%') THEN 'Oceania'
        WHEN lower(Location) LIKE lower('%Atlantic Ocean%') THEN 'Ocean, Atlantic'
        WHEN lower(Location) LIKE lower('%Somewhere in the Mid-Atlantic%') THEN 'Ocean, Atlantic'
        WHEN lower(Location) LIKE lower('%Pacific Ocean%') THEN 'Ocean, Pacific'
        WHEN lower(Location) LIKE lower('%the Pacific%') THEN 'Ocean, Pacific'
        
        WHEN lower(Location) LIKE lower('%The Ocean%') THEN 'Ocean'
        WHEN lower(Location) = lower('Ocean') THEN 'Ocean'
        
        WHEN UPPER(Location) LIKE '%HAMILTON%' THEN 'Unknown - Seen - Hamilton'
        WHEN lower(Location) LIKE lower('%London%') THEN 'Unknown - Seen - London'
        
        WHEN lower(Location) LIKE lower('%London, United Kingdom%') THEN 'United Kingdom' ---redundant
        --WHEN lower(Location) LIKE lower('%London, UK%') THEN 'United Kingdom' ---redundant
        
        
        WHEN lower(Location) = lower('Not absolute') THEN 'Offbeat'
        
        WHEN UPPER(Location) LIKE '%CANABIA%' THEN 'Offbeat' -- Canabia'
        WHEN lower(Location) LIKE lower('%Cascadia%') THEN 'North America' --'Offbeat - Cascadia'
        
        WHEN lower(Location) LIKE lower('%Computer%') THEN 'Offbeat - Computer'
        WHEN lower(Location) LIKE lower('%desk%') THEN 'Offbeat' -- Desk'
        WHEN lower(Location) LIKE lower('%Internet%') THEN 'Offbeat - Cyberspace'
        WHEN lower(Location) LIKE lower('%Cyberspace%') THEN 'Offbeat - Cyberspace'
        
        WHEN lower(Location) LIKE lower('%World%') THEN 'Offbeat - Earth'
        -- Must follow Earth, TX
        WHEN UPPER(Location) LIKE '%EARTH%' THEN 'Offbeat - Earth'
        
        WHEN UPPER(Location) LIKE '%PLANET%' THEN 'Offbeat - Earth'
        WHEN lower(Location) LIKE lower('%Sol%') THEN 'Offbeat - Space'
        WHEN lower(Location) LIKE lower('%Heaven%') THEN 'Offbeat'
        
        
        WHEN Location LIKE '%Cesky Tesin%' THEN 'Czech Republic'
        WHEN Location LIKE '%El Mosky%' THEN 'Egypt'
        WHEN Location LIKE '%Jyväskylä%' THEN 'Finland'
        WHEN Location LIKE '%Jyvaskyla%' THEN 'Finland '
        WHEN Location LIKE '%Sandusky%' THEN 'United States'
        WHEN Location LIKE '%Khmelnitsky%' THEN 'Ukraine'
        WHEN Location LIKE '%Zhukovsky%' THEN 'Russia'
        WHEN UPPER(Location) LIKE '%SKY%' THEN 'Offbeat' -- Sky'    
        
        WHEN lower(Location) LIKE lower('%Milky Way%') THEN 'Offbeat - Space'
        WHEN UPPER(Location) LIKE '%UNIVERSE%' THEN 'Offbeat - Space'
        
        WHEN lower(Location) LIKE '%outer space%' THEN 'Offbeat - space'
        WHEN lower(Location) LIKE '%space%' THEN 'Offbeat - space'
        
        WHEN UPPER(Location) LIKE '%WWW%' THEN 'Offbeat - Cyberspace'
        WHEN lower(Location) LIKE lower('%Localhost%') THEN 'Offbeat - Cyberspace'
        
        
        WHEN lower(Location) LIKE '%behind%' THEN 'Offbeat' -- behind'
        WHEN lower(Location) LIKE 'hidden' THEN 'Offbeat' -- hidden'
        WHEN lower(Location) = 'none' THEN 'Offbeat' -- none' -- there is a None, Italy
        WHEN lower(Location) LIKE '%nowhere%' THEN 'Offbeat' -- nowhere' ---- there is a Nowhere, OK
        
        --WHEN lower(Location) LIKE '%somewhere%over the rainbow%' THEN 'Offbeat - somewhere over the rainbow'
        --WHEN lower(Location) LIKE lower('somewhere') THEN 'Offbeat - Somewhere equals somewhere'
        --WHEN lower(Location) LIKE lower('(somewhere)') THEN 'Offbeat - Somewhere equals (somewhere)'
        
        --WHEN lower(Location) LIKE lower('%Somewhere around%') THEN 'Offbeat - Somewhere around'
        --WHEN lower(Location) LIKE lower('%Somewhere between%') THEN 'Offbeat - Somewhere between'
        
        WHEN lower(location) LIKE lower('%Somewhere in the US%') THEN 'United States'
        
        --WHEN lower(location) LIKE lower('%An airport terminal somewhere%') THEN 'Offbeat - Somewhere An airport terminal somewhere'
        --WHEN lower(location) LIKE lower('%Somewhere East of Where You Are%') THEN 'Offbeat - Somewhere East of Where You Are'

        --WHEN lower(location) LIKE lower('%Somewhere far beyond...%') THEN 'Offbeat - Somewhere far beyond...'       
        
        --WHEN lower(location) LIKE lower('%somewhere good%') THEN 'Offbeat - Somewhere good'
        --WHEN lower(location) LIKE lower('%Somewhere I Belong%') THEN 'Offbeat - Somewhere I Belong'
        --WHEN lower(location) LIKE lower('%Somewhere I guess...%') THEN 'Offbeat - Somewhere I guess...'
        --WHEN lower(location) LIKE lower('%Somewhere in 2011%') Then 'Offbeat - Somewhere in 2011'
        --when lower(location) LIKE lower('%Somewhere in Null....%') Then 'Offbeat - Somewhere in Null....'
        --when lower(location) LIKE lower('%somewhere in ny... maybe.%') Then 'Offbeat - Somewhere in ny... maybe.'
         
        when lower(location) LIKE lower('%Somewhere in open sea.%') Then 'Ocean' --Somewhere in open sea.'
        --when lower(location) LIKE lower('%Somewhere in the east%') Then 'Offbeat - Somewhere in the east'
        --when lower(location) LIKE lower('%Somewhere in the forests%') Then 'Offbeat - Somewhere in the forests'
        --when lower(location) LIKE lower('%Somewhere in the inner workings of the Python API%')  Then 'Offbeat - Somewhere in the inner workings'
        
        --when lower(location) LIKE lower('%Somewhere in the Valley%') Then 'Offbeat - Somewhere in the Valley'
        
        WHEN lower(Location) LIKE '%somewhere%' THEN 'Offbeat - somewhere'
        ELSE 'Unknown'
        
    END AS Country
FROM Users
      WHERE LEN(Location) > 1  and Reputation >= ##minimumReputation##
)

SELECT UsersCountry.Country,
count(1) AS 'count'
      FROM UsersCountry

GROUP BY UsersCountry.Country
--ORDER BY count(1) desc
ORDER BY Country asc

fork query

edited jan 12 14 jon.doe12449

created jul 27 11 David Freitas