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

0

Please login or register to vote for this query.

(click on this box to dismiss)

Stack Overflow

Q&A for programmers

-- List of users in Iran

SELECT
    ROW_NUMBER() OVER(ORDER BY Reputation DESC) AS [#], 
    Id AS [User Link],
    *
    
FROM
    Users
    
WHERE
     (
      lower(Location) LIKE ('%iran%')
               
      -- http://sustainablesources.com/resources/country-abbreviations/
      -- can introduce errors 
      OR Location LIKE  '%IR'
      
      OR LOWER(Location) LIKE '%ahvaz%' --Do not use %ah%az% - this includes Bahia, Brazil
      OR LOWER(Location) LIKE '%ahwaz%'
      
      OR LOWER(Location) LIKE '%alborz%'
          
      OR LOWER(Location) LIKE '%ajabshir%'
      OR LOWER(Location) LIKE '%ardebil%'
    
      OR LOWER(Location) LIKE '%azarbaijan%'
      --OR LOWER(Location) LIKE 'azerbaijan'
      
      OR lower(Location) LIKE lower('%Babol%')
      
      OR LOWER(Location) LIKE '%bafq%'
      OR LOWER(Location) LIKE '%bushehr%'
    
      OR LOWER(Location) LIKE '%esfahan%'
    
      OR LOWER(Location) LIKE '%qom%'
      OR LOWER(Location) LIKE '%golestan%'
      OR LOWER(Location) LIKE '%gorgan%'
      OR LOWER(Location) LIKE '%ham%dan%'
      
      OR LOWER(Location) LIKE '%jahrom%'
    
      OR LOWER(Location) LIKE '%karaj%'
      OR LOWER(Location) LIKE '%kashan%'
    
      OR LOWER(Location) LIKE '%kerman%' -- includes kermanshah
      
      OR LOWER(Location) LIKE '%mashhad%'
      OR LOWER(Location) LIKE '%mehrshahr%'
      
      OR LOWER(Location) LIKE '%qazvin%'
      OR LOWER(Location) LIKE '%semnan%'
      OR LOWER(Location) LIKE '%shiraz%'
      OR LOWER(Location) LIKE '%tabriz%'
      OR LOWER(Location) LIKE '%tehran%'
      OR LOWER(Location) LIKE '%urmia%'
      OR LOWER(Location) LIKE '%yazd%'
      OR LOWER(Location) LIKE '%zanjan%'
     )
   
      -- exclusions
     and not
     (
      Location LIKE '%IZMIR%'  --Turkey
      or Location LIKE 'ADHAMI PIRAN JHANDUKHEL BANNU' --Pakistan
      or lower(Location) LIKE lower('%Altamirano%') --Mexico - Spam account
      or lower(Location) LIKE lower('%Indiranagar%') --India
      
      or lower(Location) LIKE lower('%Mirandela%') --Portugal
      or lower(Location) LIKE lower('%Mirandola%') --Italy
      or lower(Location) LIKE lower('%Mirano%') --Italy
      
      or lower(Location) LIKE lower('%Pietravairano%') --Italy
      
      or lower(Location) LIKE lower('%Sapiranga%') --Brazil
      or lower(Location) LIKE lower('%Srirangam%') --India
      
      or lower(Location) LIKE lower('%Tirana%') --Albania
      or lower(Location) LIKE lower('%Tirane%') --Albania
      or lower(Location) LIKE lower('%Titirangi%') --New Zealand
     )                
         
    and LEN(Location) > 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