Simple Mysql query that excludes certain data

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Flashart80
Forum Newbie
Posts: 4
Joined: Fri Jan 02, 2009 8:23 am

Simple Mysql query that excludes certain data

Post 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
User avatar
Apollo
Forum Regular
Posts: 794
Joined: Wed Apr 30, 2008 2:34 am

Re: Simple Mysql query that excludes certain data

Post 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%')
Post Reply