Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Excluding values in full text query

Author  Topic  simpelli Starting Member 1 Post Posted - 2008-01-30 : 14:46:09 Greetings, I'm creating a web based app that includes basic and advanced search functionality. We're using SQL Server 2005 with Full Text Search enabled on the back end. The SP that performs the search builds a search string based on the options a user submits: BASIC - any string or comma separated list of words ALL WORDS - (Actually, same as basic, but from "Advanced Search") EXACT PHRASE - (Just like it sounds) EXCLUDING WORDS - Words the search should ignore. If anything is entered into any or all of the fields, the search string is constructed properly and the correct result set is returned. The query is pretty complex, involving 11 tables across 2 databases. I'm using CONTAINSTABLE(<field_name>, @searchString) on 5 of the tables, which would be where any of the possible search values would be coming from (weighting doesn't matter here). Works great, is really fast...BUT If the only option passed is EXCLUDED WORDS and nothing else ("I want to see all parts except SCREWS") it returns nothing. According to MSDN: NOT can only occur after AND, as in AND NOT. The OR NOT operator is not allowed. NOT cannot be specified before the first term (for example, CONTAINS (mycolumn, 'NOT "phrase_to_search_for" ' ). How do I construct the search string to return everything "AND NOT <excluded word list>" ? Should I not use CONTAINSTABLE and just use "WHERE CONTAINS(<field_name>, @searchString)" (I'll try this, but also looking for some input) Is it even possible to return all records if no criteria is specified? Thanks!