Subject Re: SQL Query like/not like with and/or From Mervyn Bick <invalid@invalid.invalid> Date Mon, 2 Sep 2024 15:59:06 +0200 Newsgroups dbase.getting-started On 2024/09/02 14:13, trevor wrote: > Windows 10- dBase PLUS 2.21 > > Using where  to create a sql query I have found a difference between > using 'like' and 'not like' in conjunction with or/and > Using a simplified table as example > > abname.dbf > (single field),bname >   records rows (char) > TP, JM, TPJM,SP, MP > > so using where like with or > >   select * from abname where bname like 'T%' or  bname like 'J%' > returns  rows TP,JM, TPJM >   - result correct as expected > > using where not like with or > >   select * from abname where bname not like 'T%' or  bname not like 'J%' > returns All records rows > not correct expected only SP,MP Wrong expectations. :-)   If bname doesn't start with 'T' it means it starts with any other character including 'J', 'S' and 'M'.  If bname doesn't start with 'J' it means it starts with any other character including 'T', 'S' and 'M'.  With the two test OR'ed, if either test evaluates as true then the record is included so you wind up with all the records > > However using "and" -not- "or" > >   select * from abname where bname not like 'T%'  and  bname not like J%' > returns SP, MP > as desired but unexpected as only single field row. When you AND two tests a record will only be included in the rowset where both tests evaluate as true.  Your test table has two records where bname starts with 'T', and one record each for bname starting with 'J', 'S', or 'M'.  The only time both tests evaluate as true is where bname starts with 'S' or 'M'.  If your test table had more records with bname containing values such as 'AB', 'CD' and 'XY' these would also be included in the rowset together with 'SP' and 'MP'. > > using > select * from abname where bname like 'T%' and  bname like 'J%' > returns no records as expected. Correct. The first character in bname can be any character but it can't be two characters at the same time.  A record is included in the rowset only if both tests evaluate as true. > > It would appear that 'or' with  "like" works/queries on each rows as > expected but 'and'  with "not like" appears to works/queries  the whole > table as one. > > Can anyone offer explanation where using  'and' in 'not like' gives a > correct result where I would have expected the use of 'or' to be > correct. The LIKE predicate only works with character fields. It depends, of course, on what you're doing but it is unusual to use NOT LIKE in a SELECT statement's WHERE clause as it will normally return more records in the rowset than it rejects. Mervyn.