User List: Top N - Canada - with false positive exclusions

1

Please login or register to vote for this query.

(click on this box to dismiss)

MathOverflow

Q&A for professional mathematicians

-- List of users in Canada

SELECT
    Top ##Top##
    ROW_NUMBER() OVER(ORDER BY Reputation DESC) AS [#], 
    Id AS [User Link], 
    Reputation,
    Location,
    --CreationDate,
    LastAccessDate,
    UpVotes,
    DownVotes
    
FROM
    Users
    
WHERE
     (
      lower(Location) LIKE '%canada%'
      or lower(Location) LIKE lower('%Canadur%')
      
      -- http://sustainablesources.com/resources/country-abbreviations/
      -- CA is ambiguous as it is also California
      -- Unlikely for Canadians to use CA
      
      or lower(Location) LIKE lower('%Alberta%')
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' AB'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',AB'
      or UPPER(Location) = 'AB'
      
      or lower(Location) LIKE lower('%Calgary%')
      or lower(Location) LIKE lower('%Edmonton%')
            
      or lower(Location) LIKE '%british columbia%'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' BC'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',BC'
      or UPPER(Location) = 'BC'
      
      or UPPER(Location) LIKE '%B.C.%'
      
      or lower(Location) LIKE '%abbotsford%'
      or lower(Location) LIKE '%burnaby%'
      
      OR lower(Location) LIKE '%kamloops%'
      OR lower(Location) LIKE '%kelowna%'
      
      OR lower(Location) LIKE '%vancouver%' -- ca and wa excluded later
      
      OR lower(Location) LIKE '%manitoba%'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' MB'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',MB'      
      or UPPER(Location) = 'MB'
      
      or lower(Location) LIKE '%winnipeg%'
      or lower(Location) LIKE lower('%winterpeg%')
      
      
      OR lower(Location) LIKE '%new brunswick%' -- %new brunswick, nj% excluded later
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' NB'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',NB'
      or UPPER(Location) = 'NB'
      
      OR lower(Location) LIKE '%fredericton%'
      OR lower(Location) LIKE '%moncton%'
      
      OR lower(Location) LIKE '%saint john n%'
      OR lower(Location) LIKE '%saint john, n%'
      OR lower(Location) LIKE '%saint john,n%'
      
      OR lower(Location) LIKE '%saint john ca%'
      OR lower(Location) LIKE '%saint john, ca%'
      OR lower(Location) LIKE '%saint john,ca%'
      
      
      OR lower(Location) LIKE '%newfoundland%'
      --or UPPER(RIGHT(RTRIM(Location), 3)) = ' NL' -- Matches Netherlands
      --or UPPER(RIGHT(RTRIM(Location), 3)) = ',NL' -- Matches Netherlands
      --or UPPER(Location) = 'NL' -- Matches Netherlands
      
      OR lower(Location) LIKE '%st%john''s%'
      
      
      OR lower(Location) LIKE '%northwest terr%'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' NT'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',NT'
      or UPPER(Location) = 'NT'
      
      or lower(Location) LIKE '%yellowknife%'
      
      
      OR lower(Location) LIKE '%nova scotia%'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' NS'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',NS'
      or UPPER(Location) = 'NS'
      
      or lower(Location) LIKE lower('%Sydney%Ca%')
      
      or lower(Location) LIKE '%halifax%ns%'
      or lower(Location) LIKE '%halifax%nova%'
      or lower(Location) LIKE '%halifax%ca%'
      
      or lower(Location) LIKE '%wolfville%'
      
      
      OR lower(Location) LIKE '%nunavut%'  
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' NU'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',NU'
      or UPPER(Location) = 'NU'
      
      OR lower(Location) LIKE '%ontario%' --  '%Ontario, CA' and %Ontario, CAL%'excluded later 
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' ON'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',ON'
      or UPPER(Location) = 'ON'
      
      OR lower(Location) LIKE '%acton on%'
      OR lower(Location) LIKE '%acton, on%'
      OR lower(Location) LIKE '%acton,on%'
      
      OR lower(Location) LIKE '%acton can%'
      OR lower(Location) LIKE '%acton, can%'
      OR lower(Location) LIKE '%acton,can%'
            
      OR lower(Location) LIKE '%ajax%'
      
      OR lower(Location) LIKE '%aurora on%'
      OR lower(Location) LIKE '%aurora, on%'
      OR lower(Location) LIKE '%aurora,on%'
      
      OR lower(Location) LIKE '%aurora ca%'
      OR lower(Location) LIKE '%aurora, ca%'
      OR lower(Location) LIKE '%aurora,ca%'
      
      OR lower(Location) LIKE '%barrie%'
      
      OR lower(Location) LIKE '%belleville on%'
      OR lower(Location) LIKE '%belleville, on%'
      OR lower(Location) LIKE '%belleville,on%'
      
      OR lower(Location) LIKE '%belleville ca%'
      OR lower(Location) LIKE '%belleville, ca%'
      OR lower(Location) LIKE '%belleville,ca%'
     
      OR lower(Location) LIKE '%bowmanville%'
      OR lower(Location) LIKE '%brampton%'
      
      OR lower(Location) LIKE '%brantford%'
      OR lower(Location) LIKE '%brockville%'
      
      OR lower(Location) LIKE '%burlington on%'
      OR lower(Location) LIKE '%burlington, on%'
      OR lower(Location) LIKE '%burlington,on%'
      
      OR lower(Location) LIKE '%burlington ca%'
      OR lower(Location) LIKE '%burlington, ca%'
      OR lower(Location) LIKE '%burlington,ca%'
      
      OR lower(Location) LIKE '%caledon on%'
      OR lower(Location) LIKE '%caledon, on%'
      OR lower(Location) LIKE '%caledon,on%'
      
      OR lower(Location) LIKE '%caledon ca%'
      OR lower(Location) LIKE '%caledon, ca%'
      OR lower(Location) LIKE '%caledon,ca%'
      
      OR lower(Location) LIKE '%cambridge on%'
      OR lower(Location) LIKE '%cambridge, on%'
      OR lower(Location) LIKE '%cambridge,on%'
      
      OR lower(Location) LIKE '%cambridge ca%'
      OR lower(Location) LIKE '%cambridge, ca%'
      OR lower(Location) LIKE '%cambridge,ca%'
      
      OR lower(Location) LIKE '%chatham on%'
      OR lower(Location) LIKE '%chatham, on%'
      OR lower(Location) LIKE '%chatham,on%'
      
      OR lower(Location) LIKE '%chatham ca%'
      OR lower(Location) LIKE '%chatham, ca%'
      OR lower(Location) LIKE '%chatham,ca%'
      
      OR lower(Location) LIKE '%chatham-kent%'
      OR lower(Location) LIKE '%coe hill%'
      OR lower(Location) LIKE '%collingwood%'
      
      OR lower(Location) LIKE '%elmira on%'
      OR lower(Location) LIKE '%elmira, on%'
      OR lower(Location) LIKE '%elmira,on%'
      
      OR lower(Location) LIKE '%elmira ca%'
      OR lower(Location) LIKE '%elmira, ca%'
      OR lower(Location) LIKE '%elmira,ca%'
      
      OR lower(Location) LIKE '%fort erie%'
      
      OR lower(Location) LIKE '%guelph%'
      
      OR lower(Location) LIKE '%hamilton on%'
      OR lower(Location) LIKE '%hamilton, on%'
      OR lower(Location) LIKE '%hamilton,on%'
      
      OR lower(Location) LIKE '%hamilton ca%'
      OR lower(Location) LIKE '%hamilton, ca%'
      OR lower(Location) LIKE '%hamilton,ca%'
            
      OR lower(Location) LIKE '%kingston on%'
      OR lower(Location) LIKE '%kingston, on%'
      OR lower(Location) LIKE '%kingston,on%'
      
      OR lower(Location) LIKE '%kingston ca%'
      OR lower(Location) LIKE '%kingston, ca%'
      OR lower(Location) LIKE '%kingston,ca%'
      
      OR lower(Location) LIKE '%kitchener%'
      
      OR lower(Location) LIKE '%kw on%'
      OR lower(Location) LIKE '%kw, on%'
      OR lower(Location) LIKE '%kw,on%'
      
      OR lower(Location) LIKE '%leamington on%'
      OR lower(Location) LIKE '%leamington, on%'
      OR lower(Location) LIKE '%leamington,on%'
      
      OR lower(Location) LIKE '%leamington ca%'
      OR lower(Location) LIKE '%leamington, ca%'
      OR lower(Location) LIKE '%leamington,ca%'
      
      OR lower(Location) LIKE '%london on%'
      OR lower(Location) LIKE '%london, on%'
      OR lower(Location) LIKE '%london,on%'
      
      OR lower(Location) LIKE '%london can%'
      OR lower(Location) LIKE '%london, can%'
      OR lower(Location) LIKE '%london,can%'
      
      OR lower(Location) LIKE '%markham%on%'
      OR lower(Location) LIKE '%markham%ca%'
      
      OR lower(Location) LIKE '%milton on%'
      OR lower(Location) LIKE '%milton, on%'
      OR lower(Location) LIKE '%milton,on%'
      
      OR lower(Location) LIKE '%milton ca%'
      OR lower(Location) LIKE '%milton, ca%'
      OR lower(Location) LIKE '%milton,ca%'
      
      OR lower(Location) LIKE '%mississauga%'
      
      OR lower(Location) LIKE '%newmarket on%'
      OR lower(Location) LIKE '%newmarket, on%'
      OR lower(Location) LIKE '%newmarket,on%'
      
      OR lower(Location) LIKE '%newmarket ca%'
      OR lower(Location) LIKE '%newmarket, ca%'
      OR lower(Location) LIKE '%newmarket,ca%'
      
      OR lower(Location) LIKE '%north bay on%'
      OR lower(Location) LIKE '%north bay, on%'
      OR lower(Location) LIKE '%north bay,on%'
      
      OR lower(Location) LIKE '%north bay can%'
      OR lower(Location) LIKE '%north bay, can%'
      OR lower(Location) LIKE '%north bay,can%'
      
      -- north bay, California
      
      OR lower(Location) LIKE '%oakville on%'
      OR lower(Location) LIKE '%oakville, on%'
      OR lower(Location) LIKE '%oakville,on%'
      
      OR lower(Location) LIKE '%oakville ca%'
      OR lower(Location) LIKE '%oakville, ca%'
      OR lower(Location) LIKE '%oakville,ca%'
      
      --OR lower(Location) LIKE '%ottawa on%'
      --OR lower(Location) LIKE '%ottawa, on%'
      --OR lower(Location) LIKE '%ottawa,on%'
      
      --OR lower(Location) LIKE '%ottawa ca%'
      --OR lower(Location) LIKE '%ottawa, ca%'
      --OR lower(Location) LIKE '%ottawa,ca%'
            
      OR lower(Location) LIKE '%ottawa%' -- %ottawa%il% excluded later
      
      OR lower(Location) LIKE '%perth on%'
      OR lower(Location) LIKE '%perth, on%'
      OR lower(Location) LIKE '%perth,on%'
      
      OR lower(Location) LIKE '%perth ca%'
      OR lower(Location) LIKE '%perth, ca%'
      OR lower(Location) LIKE '%perth,ca%'
      
      OR lower(Location) LIKE '%peterborough on%'
      OR lower(Location) LIKE '%peterborough, on%'
      OR lower(Location) LIKE '%peterborough,on%'
      
      OR lower(Location) LIKE '%peterborough ca%'
      OR lower(Location) LIKE '%peterborough, ca%'
      OR lower(Location) LIKE '%peterborough,ca%'
      
      OR lower(Location) LIKE '%pickering on%'
      OR lower(Location) LIKE '%pickering, on%'
      OR lower(Location) LIKE '%pickering,on%'
      
      OR lower(Location) LIKE '%pickering ca%'
      OR lower(Location) LIKE '%pickering, ca%'
      OR lower(Location) LIKE '%pickering,ca%'
      
      OR lower(Location) LIKE '%richmond hill on%'
      OR lower(Location) LIKE '%richmond hill, on%'
      OR lower(Location) LIKE '%richmond hill,on%'
      
      OR lower(Location) LIKE '%richmond hill ca%'
      OR lower(Location) LIKE '%richmond hill, ca%'
      OR lower(Location) LIKE '%richmond hill,ca%'
      
      OR lower(Location) LIKE '%sarnia%'
      
      --Sault Ste. Marie / Sault Ste Marie 
      OR lower(Location) LIKE '% marie on%'
      OR lower(Location) LIKE '% marie, on%'
      OR lower(Location) LIKE '% marie,on%'
      
      OR lower(Location) LIKE '% marie ca%'
      OR lower(Location) LIKE '% marie, ca%'
      OR lower(Location) LIKE '% marie,ca%'
      
      OR lower(Location) LIKE '%st. catharines%'
      
      OR lower(Location) LIKE '%st. thomas%'
      
      OR lower(Location) LIKE '%stratford on%'
      OR lower(Location) LIKE '%stratford, on%'
      OR lower(Location) LIKE '%stratford,on%'
      
      OR lower(Location) LIKE '%stratford ca%'
      OR lower(Location) LIKE '%stratford, ca%'
      OR lower(Location) LIKE '%stratford,ca%'

      OR lower(Location) LIKE '%sudbury on%'
      OR lower(Location) LIKE '%sudbury, on%'
      OR lower(Location) LIKE '%sudbury,on%'
      
      OR lower(Location) LIKE '%sudbury ca%'
      OR lower(Location) LIKE '%sudbury, ca%'
      OR lower(Location) LIKE '%sudbury,ca%'
      
      OR lower(Location) LIKE '%thunder bay%'
      
      OR lower(Location) LIKE '%timmins on%'
      OR lower(Location) LIKE '%timmins, on%'
      OR lower(Location) LIKE '%timmins,on%'
      
      OR lower(Location) LIKE '%timmins ca%'
      OR lower(Location) LIKE '%timmins, ca%'
      OR lower(Location) LIKE '%timmins,ca%'
      
      OR lower(Location) LIKE '%toronto%'
      OR Location LIKE '%GTA%'
      
      OR lower(Location) LIKE '%vaughan%'
      
      OR lower(Location) LIKE '%university of waterloo%'
      OR lower(Location) LIKE '%waterloo on%'
      OR lower(Location) LIKE '%waterloo, on%'
      OR lower(Location) LIKE '%waterloo,on%'
      
      OR lower(Location) LIKE '%waterloo ca%'
      OR lower(Location) LIKE '%waterloo, ca%'
      OR lower(Location) LIKE '%waterloo,ca%'
      
      or lower(Location) LIKE '%welland%'
      or lower(Location) LIKE '%whitby%'
      
      OR lower(Location) LIKE '%windsor on%'
      OR lower(Location) LIKE '%windsor, on%'
      OR lower(Location) LIKE '%windsor,on%'
      
      OR lower(Location) LIKE '%windsor ca%'
      OR lower(Location) LIKE '%windsor, ca%'
      OR lower(Location) LIKE '%windsor,ca%'
      
      
      OR lower(Location) LIKE '%woodbridge on%'
      OR lower(Location) LIKE '%woodbridge, on%'
      OR lower(Location) LIKE '%woodbridge,on%'
      
      OR lower(Location) LIKE '%woodbridge ca%'
      OR lower(Location) LIKE '%woodbridge, ca%'
      OR lower(Location) LIKE '%woodbridge,ca%'
            
      
      OR lower(Location) LIKE '%prince edward is%'
      --or UPPER(RIGHT(RTRIM(Location), 3)) = ' PE' -- Matches Peru
      --or UPPER(RIGHT(RTRIM(Location), 3)) = ',PE' -- Matches Peru
      --or UPPER(Location) = 'PE' -- Matches Peru
      
      OR lower(Location) LIKE '%charlottetown%'
      OR lower(Location) LIKE '%charlottetown%'
      OR lower(Location) LIKE '%charlottetown%'
      
      
      OR lower(Location) LIKE '%qu%bec%'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' QC'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',QC'
      or UPPER(Location) = 'QC'
            
      OR lower(Location) LIKE '%qc%'
      
      or UPPER(Location) LIKE '%CHICOUTIMI%'
      OR lower(Location) LIKE '%gatineau%'
      or lower(Location) LIKE lower('%Montr%al%')
      OR lower(Location) LIKE '%sherbrooke%'
      
      OR lower(Location) LIKE '%saskatchewan%'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' SK'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',SK'
      or UPPER(Location) = 'SK'
      
      OR lower(Location) LIKE '%regina%'
      OR lower(Location) LIKE '%saskatoon%'
      
      
      OR lower(Location) LIKE '%yukon%'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ' YT'
      or UPPER(RIGHT(RTRIM(Location), 3)) = ',YT'
      or UPPER(Location) = 'YT'
      
      )
     
     -- exclusions
     and not
     (
      lower(location) like '%california%'
      or lower(location) like '%new brunswick, nj%'
      
      or lower(Location) LIKE '%kottawa%'
      
      or lower(Location) LIKE '%ottawa%il%'
      
      or lower(Location) LIKE '%ottawa lake%'      
      
      or lower(Location) LIKE '%markham%il%'
      
      or lower(Location) LIKE '%newfoundland, pa%'
      
      or lower(Location) LIKE '%vancouver ca'
      or lower(Location) LIKE '%vancouver, ca'
      or lower(Location) LIKE '%vancouver,ca'
      
      or lower(Location) LIKE '%vancouver wa%'
      or lower(Location) LIKE '%vancouver, wa%'
      or lower(Location) LIKE '%vancouver,wa%'
      
      or lower(Location) LIKE '%ontario ca'
      or lower(Location) LIKE '%ontario, ca'
      or lower(Location) LIKE '%ontario,ca'
      
      or lower(Location) LIKE '%ontario cal%'
      or lower(Location) LIKE '%ontario, cal%'
      or lower(Location) LIKE '%ontario,cal%'
      
      or lower(Location) LIKE '%mexicali%' -- May match b.c.
      or lower(Location) LIKE '%mexico%' -- May match b.c.
     )                 
         
    and LEN(Location) > 1 
    --and Reputation > 1
    
    -- Temporarily sort by location to more easily pick out false positive entries
    ORDER BY Location ASC;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results