Page 1 of 1

Simple Mysql query that excludes certain data

Posted: Wed Mar 18, 2009 10:34 am
by Flashart80
Hi everyone, I would really appreciate some help!

I have searched the forum for an answer to this very simple question but I must be going blind (wood and trees and all that..)

I have a very simple database that holds lots of codes. I want to retrieve all these codes but exclude ones that have certain characters in them.

For instance say I have a code like "ABC-123-456-789-DEF" (without quotations), I want to be able to retrieve all the codes that don't have "456" in them.

I have tried a "NOT" statement with and this doesn't do what I want.

This is what I have

Code: Select all

SELECT code 
from code_data
where code like ('%123-456-789%')
gets me all the codes with the above criteria in them. I then tried using the NOT statement like so

Code: Select all

SELECT code 
from code_data
where code like ('%123-456-789%')
and  not (code = '%456%') 
Using that code I get the same results. Presumably the original WHERE statment overrides the NOT.

I'm sure this is very simple but I just can't get it to work!

Many thanks for all your help!

regards
Peter

Re: Simple Mysql query that excludes certain data

Posted: Wed Mar 18, 2009 11:10 am
by Apollo
You can only use % wildcards with LIKE, not = (unless you literally mean comparing to a % character).

Try this instead:

Code: Select all

SELECT code FROM code_data WHERE NOT (code LIKE '%456%')