CODEWITHSUNDEEP
sqlpostgresql
ziggy
ziggy

Reputation: 1538

postgresql two NOT LIKE clauses returning wrong results

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?

Upvotes: 0

Views: 206

Answers (1)

LukStorms
LukStorms

Reputation: 29667

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

Upvotes: 2

Related Questions