I have a table with 88 codes. I am writing a simple select statement that does not select the codes that start with 10
or 18
select distinct pcl_mun from exemptions.modiv_parcels_2015 where (pcl_mun NOT LIKE '10%') or (pcl_mun NOT LIKE '18%') order by pcl_mun
one would assume this would work but it returns ALL the results
"0233" "0242" "1001" "1002" "1003" "1004" "1005" "1006" "1012" "1013" "1014" "1015" "1018" "1019" "1020" "1024" "1025" "1401" "1402" "1403" "1406" "1407" "1408" "1409" "1412" "1413" "1414" "1415" "1418" "1419" "1420" "1421" "1422" "1423" "1424" "1425" "1426" "1427" "1428" "1429" "1431" "1432" "1433" "1434" "1435" "1436" "1437" "1438" "1439" "1601" "1609" "1611" "1613" "1615" "1801" "1802" "1803" "1807" "1815" "1904" "1906" "1908" "1909" "1911" "1912" "1916" "1918" "1919" "1922" "2101" "2102" "2103" "2105" "2106" "2107" "2108" "2110" "2111" "2112" "2114" "2115" "2116" "2117" "2119" "2120" "2121" "2122" "2123"
If i just run each one of those like clauses by themselves they return the correct results. what am I doing wrong here?
Advertisement
Answer
When combining NOT LIKE
then AND
should be used instead of OR
select distinct pcl_mun from exemptions.modiv_parcels_2015 where pcl_mun NOT LIKE '10%' AND pcl_mun NOT LIKE '18%' order by pcl_mun
It’s a logical thing that can confuse sometimes.
Simplified examples:
('18' NOT LIKE '10%') OR ('18' NOT LIKE '18%') --> true OR false --> true ('18' NOT LIKE '10%') AND ('18' NOT LIKE '18%') --> true AND false --> FALSE ('14' NOT LIKE '10%') OR ('14' NOT LIKE '18%') --> true OR true --> TRUE ('14' NOT LIKE '10%') AND ('14' NOT LIKE '18%') --> true AND true --> TRUE
Because it’s different when negating a combination of LIKE
‘s.
Then OR
should be used instead.
NOT ('18' LIKE '10%' OR '18' LIKE '18%') --> NOT(false OR true) --> FALSE NOT ('18' LIKE '10%' AND '18' LIKE '18%') --> NOT(false AND true) --> true NOT ('14' LIKE '10%' OR '14' LIKE '18%') --> NOT(false OR false) --> TRUE NOT ('14' LIKE '10%' AND '14' LIKE '18%') --> NOT(false AND false) --> TRUE