Test: Remove locations not related to India

0

Please login or register to vote for this query.

(click on this box to dismiss)

Locations are moved form the results. Any remaining not India related can be added to to list to exclude.

Amateur Radio

Q&A for amateur radio enthusiasts

SELECT   RankNo, 
         [User Link], 
         Reputation, 
         --DisplayName, 
         Location

FROM
         (
            SELECT Id [User Link], Reputation, Location,
                   DENSE_RANK() OVER (ORDER BY Reputation DESC) RankNo
            FROM   Users
            WHERE 
           (lower(Location) LIKE '%india%' 
           OR Location LIKE '%IND%')
              
              and not(lower(Location) LIKE '%indiana%'
                      
                   OR lower(Location) LIKE '%independence%'
                   OR lower(Location) LIKE '%indonesia%' 
                 
                   OR lower (Location) LIKE '%behind%'
                   OR lower(Location) LIKE '%wernigerode%' --Germany
                   OR lower(Location) LIKE '%goatlandia%'  -- not Goa
                   OR lower(Location) LIKE '%goatswood%'   -- not Goa
                  
                   OR lower(Location) LIKE '%indias%'   -- Colombia
                   
                    --Indian Rocks Beach, FL
                   OR lower(Location) LIKE '%indian rocks%'
                   
                   --Indianola, IA and Indianola, WA
                   OR lower(Location) LIKE '%indianola%'
                  
                  --Iran, Sanandaj (matches ANAND)
                  OR lower(Location) LIKE '%sanandaj%'
                   
                   -- --
                   
           -- removing these so new possible exclusions are found
                   
              OR UPPER(Location) LIKE '%AGRA%'
              OR lower(Location) LIKE '%adipur%'
              OR lower(Location) LIKE '%adiyakkamangalam%'
              OR lower(Location) LIKE '%adoor%'
              OR lower(Location) LIKE '%adipur%'
              
              OR lower(Location) LIKE '%agartala%'
              
              OR lower(Location) LIKE '%ahamadabad%'
              OR lower(Location) LIKE '%ahamdabad%'
              
              OR lower(Location) LIKE '%ahemadabad%'
              OR lower(Location) LIKE '%ahemdabad%'
              
              OR UPPER(Location) LIKE '%AHMADABAD%'

              OR lower(Location) LIKE '%ahmdawad%'
              
              OR UPPER(Location) LIKE '%AHMEDABAD%'
              OR UPPER(Location) LIKE '%AHMEDABD%'
              
              OR lower(Location) LIKE '%aizawl%'
              
              OR UPPER(Location) LIKE '%AJMER%'
              
              OR lower(Location) LIKE '%akola%'
              OR UPPER(Location) LIKE '%ALLAHABAD%'
              OR UPPER(Location) LIKE '%AMRITSAR%'
              OR UPPER(Location) LIKE '%ANAND%'
              
              OR lower(Location) LIKE '%anklesvar%'
              
              OR UPPER(Location) LIKE '%AURANGABAD%'
              
              
              
              OR UPPER(Location) LIKE '%BANGALORE%' 
             
              OR UPPER(Location) LIKE '%BANGALARU%'
              OR lower(Location) LIKE '%banglore%'
              OR UPPER(Location) LIKE '%BARODA%'  

              OR UPPER(Location) LIKE '%BANGALURU%'

              OR lower(Location) LIKE '%belgaum%'
             
              OR UPPER(Location) LIKE '%BENGALURU%'
              --
              OR lower(Location) LIKE '%bhavnagar%'
              OR UPPER(Location) LIKE '%BHILAI NAGAR%'
              OR UPPER(Location) LIKE '%BHILWARA%'  
              OR UPPER(Location) LIKE '%BHOPAL%'  
              OR UPPER(Location) LIKE '%BHUBNESHWAR%' 
              OR UPPER(Location) LIKE '%BHUBANESWAR%'
              OR UPPER(Location) LIKE '%BIKANER%'
              
              OR UPPER(Location) LIKE '%CALICUT%'
              OR UPPER(Location) LIKE '%CHANDGAD%'

              OR UPPER(Location) LIKE '%CHANDIGARH%'  
              OR UPPER(Location) LIKE '%CHANDIGHAR%'  
              OR UPPER(Location) LIKE '%CHANDIGHARH%'  

              OR UPPER(Location) LIKE '%CHENNAI%' 
              OR UPPER(Location) LIKE '%COCHIN%'
              OR UPPER(Location) LIKE '%COIMBATORE%' 
              
              OR UPPER(Location) LIKE '%DEHRADUN%'

              OR lower(Location) LIKE '%dehli%'
              OR UPPER(Location) LIKE '%DELHI%' 
              OR UPPER(Location) LIKE '%DHENKANAL%' 
              OR UPPER(Location) LIKE '%DIASPARK%' 
              
              OR UPPER(Location) LIKE '%DOMBIVLI%'
              
              OR UPPER(Location) LIKE '%ERODE%' 

              OR UPPER(Location) LIKE '%GANDHINAGAR%' 
              
              OR lower(location) like ('goa%') --leading characters
             
             
               OR UPPER(Location) LIKE '%GWALIOR%'
              
              OR UPPER(Location) LIKE '%GUJARAT%'
              OR UPPER(Location) LIKE '%GURGAON%'
              
              OR lower(Location) LIKE '%hindoli%'
              OR lower(Location) LIKE '%hubli-dharwad%'
              
              OR UPPER(Location) LIKE '%HYDERABAD%'  
              OR lower(Location) LIKE '%hyerabad%'
              
              OR UPPER(Location) LIKE '%INDORE%'  
              OR lower(Location) LIKE '%imphal%'
              
              OR lower(Location) LIKE '%jabalpur%'
              
              OR UPPER(Location) LIKE '%JAIPUR%' 
              OR UPPER(Location) LIKE '%JAMKALYANPUR%'  
             
              OR lower(Location) LIKE '%jamshedpur%'
              
              OR lower(Location) LIKE '%jodhpur%'
              OR UPPER(Location) LIKE '%JUNAGADH%'
              
              --
              OR lower(Location) LIKE '%kandla%'
              OR lower(Location) LIKE '%kanpur%'
              
              --
              
              OR UPPER(Location) LIKE '%KERALA%'
              
              OR UPPER(Location) LIKE '%KHARAGPUR%'
              OR UPPER(Location) LIKE '%KOCHI%' --Includes KOCHIN
              OR UPPER(Location) LIKE '%KOLKATA%'
          
              OR lower(Location) LIKE '%kota%' --Includes kotar
              
              OR lower(Location) LIKE '%kozhikode%'
              OR lower(Location) LIKE '%krishnagiri%'
             
              OR lower(Location) LIKE '%lucknow%'
              OR UPPER(Location) LIKE '%LUDHIANA%'  
               -- 
              OR lower(Location) LIKE '%madras%' 
              
              OR UPPER(Location) LIKE '%MADURAI%'  
              OR UPPER(Location) LIKE '%MAHARASHTRA%' 
              
              OR UPPER(Location) LIKE '%MANGALORE%'
              --
              OR lower(Location) LIKE '%marthandam%' 
              OR lower(Location) LIKE '%mohali%' 
              --
              OR UPPER(Location) LIKE '%MULUND%'  
              OR UPPER(Location) LIKE '%MUMBAI%'  


              OR lower(Location) LIKE '%mysore%' 
              
              --
              OR lower(Location) LIKE '%nagercoil%' 
              OR lower(Location) LIKE '%nagpur%' 
              OR lower(Location) LIKE '%nainital%'
              --
              OR UPPER(Location) LIKE '%NASHIK%'  
              OR UPPER(Location) LIKE '%NOIDA%'  
              
              --
              OR lower(Location) LIKE '%palakkad%'
              OR lower(Location) LIKE '%pandva%'
              OR lower(Location) LIKE '%panamaram%'
              --
              OR lower(Location) LIKE '%pathankot%'
              OR lower(Location) LIKE '%patiala%'
              --
              
              OR UPPER(Location) LIKE '%PATNA%' 
              --
              OR lower(Location) LIKE '%puducherry%' 
              OR UPPER(Location) LIKE '%PONDICHERRY%' 
              --
              OR lower(Location) LIKE '%powai%'
              OR UPPER(Location) LIKE '%PUNE%'  
--
--
              OR lower(Location) LIKE '%radaur%'
              
              
              OR lower(Location) LIKE '%rajankollur%'
              
              
              OR UPPER(Location) LIKE '%RAJASTHAN%' 
              OR UPPER(Location) LIKE '%RAJKOT%'
              
              OR lower(Location) LIKE '%ranchi%'
              
              OR UPPER(Location) LIKE '%SHIMLA%'
              --
              OR lower(Location) LIKE '%sidhpur%'
             
              OR UPPER(Location) LIKE '%SOLAPUR%'
              
              --
              OR lower(Location) LIKE '%sonipat%'
              
              OR UPPER(Location) LIKE '%SURAT%'  
              OR UPPER(Location) LIKE '%TAMIL NADU%'
              
              OR lower(Location) LIKE '%tamilnadu%'
              
              OR lower(Location) LIKE '%thiruvananthapuram%' 
  
              OR lower(Location) LIKE '%tirupattur%' 
              OR lower(Location) LIKE '%tiruppur%' 
              --
              
              OR UPPER(Location) LIKE '%TRIVANDRUM%'  

              OR UPPER(Location) LIKE '%UDAIPUR%' 
              --
              OR lower(Location) LIKE '%vadodara%' 
              --
              OR lower(Location) LIKE '%vasai%' 
              
              OR UPPER(Location) LIKE '%VIJAYAWADA%' 

              OR lower(Location) LIKE '%virudhunagar%'  
              OR UPPER(Location) LIKE '%VISHAKAPATNAM%'  
              OR UPPER(Location) LIKE '%VIZAG%'
              --
              OR lower(Location) LIKE '%warangal%'      
                   )
                                                         
              ) derivedtable

WHERE  LEN(Location) > 1  and  RankNo <= ##MaximumRankNo## and Reputation >= ##minimumReputation##
--ORDER    BY RankNo
ORDER    BY location

Enter Parameters

Options:
Switch to meta site
loading Hold tight while we fetch your results