Home » SQL & PL/SQL » SQL & PL/SQL » multiple NOT LIKE clause's
multiple NOT LIKE clause's [message #198081] Sat, 14 October 2006 09:16 Go to next message
DallasMan
Messages: 2
Registered: October 2006
Location: Dallas
Junior Member
I am trying to list data from one table with multiple NOT LIKE clause's but cannot get it to work correctly. I can get a single NOT LIKE clause to work fine.

I want to list all gadgets that
do not start with "MX"
do not start with "SW"
do not end with "BI"
do not end with "AI"

here is what I tried:
This query is embedded in perl cgi script.

The code below still list everything. I think I can understand why, but I cannot figure out how to get around it.

I have also tried "!=" instead of "not like"
I have also tried "and" intead of "or"
I have tried placing each in it's own perentheses.
i.e.
where (GADGET_NME not like 'MX') or (GADGET_NME not like ...


select GADGET_NME
      from ALL_GADGETS
      where (GADGET_NME not like 'MX%' or
             GADGET_NME not like 'SW%' or
             GADGET_NME not like '%BI' or
             GADGET_NME not like '%AI')  


Re: multiple NOT LIKE clause's [message #198082 is a reply to message #198081] Sat, 14 October 2006 09:27 Go to previous messageGo to next message
BlackSwan
Messages: 22718
Registered: January 2009
Senior Member
Alternatively it might work to use multiple MINUS clauses
select GADGET_NME
from ALL_GADGETS
minus
select GADGET_NME where GADGET_NME like 'MX%'
minus
select GADGET_NME where GADGET_NME like 'SW%'
Re: multiple NOT LIKE clause's [message #198103 is a reply to message #198081] Sat, 14 October 2006 21:20 Go to previous messageGo to next message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Show us the code AND the results when you use ANDs between the predicates.

Ross Leishman
icon10.gif  Re: multiple NOT LIKE clause's [message #198141 is a reply to message #198103] Sun, 15 October 2006 15:11 Go to previous messageGo to next message
DallasMan
Messages: 2
Registered: October 2006
Location: Dallas
Junior Member
I revisited using the "ANDs between the predicates" and this seemed to work as I wanted and expected.

I don't know what I was doing incorrectly before with the 'and'.

It seems so easy now, but when I can't get what I am looking for "This stuff can be frustrating" Mad

This is a great site,
Thank you for all replies.

"This worked"
select GADGET_NME
      from ALL_GADGETS
      where (GADGET_NME not like 'MX%' and
             GADGET_NME not like 'SW%' and
             GADGET_NME not like '%BI' and
             GADGET_NME not like '%AI')


"This worked"
select GADGET_NME
      from ALL_GADGETS
      where loc = 'DAL'
      and GADGET_NME not like 'MX%' 
      and GADGET_NME not like 'SW%' 
      and GADGET_NME not like '%BI' 
      and GADGET_NME not like '%AI'
Re: multiple NOT LIKE clause's [message #198149 is a reply to message #198141] Sun, 15 October 2006 19:17 Go to previous message
rleishman
Messages: 3700
Registered: October 2005
Location: Melbourne, Australia
Senior Member
That's good. I wasn't looking forward to the trip back to Uni to tell Professor Goldschlager that we wasted a semester on Logic. Wink

Ross Leishman

[Updated on: Sun, 15 October 2006 19:18]

Report message to a moderator

Previous Topic: sql query
Next Topic: SQl Query
Goto Forum:
  


Current Time: Fri Aug 29 13:33:06 CDT 2014

Total time taken to generate the page: 0.11114 seconds