Writting a Where cluse with various conditions

[Shrum]

I'm going to write a search engine function for my content display script but I had some questions about how best to deal with structiong the WHERE clause of the SQL. I've looked online but this is typically a slightly hard core for most people so there is nothing that I have found that covers this in terms of SQL.

Take for example the following search string:

"test this" +foo -bar otherwise

What this should do is return records that:

1. contains "test this" (%test this%)
2. contains 'foo'(AND '%foo%')
3. OR contain 'otherwise' (OR '%otherwise%')
4. NOT contain 'bar' (<> '%bar%')

Anyways, short of writing a small WHERE clause novel, is there a way that I can nest the WHERE statements so that it appears like:

Where `field` like '%test this%' (AND '%foo%' (OR '%otherwise%' (<> '%bar%')))

This is just a un-educated guess but you should be able to see where I'm trying to go with this. What is the best way to structure a WHERE clause of this nature?

TIA

Sean Shrum

http://www.shrum.net

 

[swampBoogie]

You must specify each predicate explicitly

Where (c1 like '%foo%' 
   and c1 not like '%bar%')
   and (c1 like '%test this%' 
    or c1 like '%otherwise%')

Why field? Tables have columns.

 

[SQLSister]

Since this is the general ansi Forum , I don't know what db you are using, but generally the use of aa wildcard as the first character precludes the use of indexes making the query very slow. In SQL Server, this type of querying is often better done by using full-text search. I don't know what the equivalents for other dbs might be, but if you have a large table that you are querying thus, it might be worth it to see if you can find some sort of equivalent.

Questions about posting. See faq183-874