| Subject | Re: SQL Query like/not like with and/or |
| From | trevor <bob@bob.com> |
| Date | Mon, 02 Sep 2024 17:36:15 -0400 |
| Newsgroups | dbase.getting-started |
Mervyn Bick wrote: > 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. Mervyn, Thanks for your reply (once again).Thanks for the explanation. I obviously need to brush up on my logic and work through the steps more thoroughly. Trevor | |