Exclude values in Full Text Search query

[simpelli]

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)
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. 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)

Thanks!

 

[Qik3Coder]

cheat the system. I have to do it sometimes on a full cartesian (sp?) join

where 1 = 1
AND NOT (select X)

-Sometimes the answer to your question is the hack that works

 

[simpelli]

Thanks Qik3Coder, I forgot about that little trick, but it does not seem to work with FT Catalog searching. Unless you know how to properly construct the search string to pass in... I tried several different ways

The more I look into this the more I'm convinced that you cannot simply return all records and exclude some based on criteria. The exclusion must be used with a filter, along the lines of "Filter String first AND NOT Exclusion String" following.

 

[Qik3Coder]

Well, what if you look for a space? And then not whatever else?

-Sometimes the answer to your question is the hack that works

 

[firthy]

Did you find the answer to your question as i have the same issue with full text search and excluding words. Could you post what your solution was?

Thnx

firthy

 

[PatriciaObreja]

Please read here about removing noise words, and maybe you'll be able to use:
Where Contains(Word, '(" " AND NOT "SCREWS")')

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[PatriciaObreja]

This is the link:

http://retrowebdev.blogspot.com/2006/09/removing-sql-server-full-text-noise.html

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[firthy]

Hi,

Yes i have already removed noise words with the function:

Public Function removeNoiseWords(ByVal str As String) As String

Dim noiseWordPattern As String = "(?<word>\b(about|1|after|2|all|also|3|an|4|and|5|another|6|any|7|are|8|as|
9|at|0|be|$|because|been|before|being|between|both|but|by|came|can|come|
could|did|do|does|each|else|for|from|get|got|has|had|he|have|her|here|him|
himself|his|how|if|in|into|is|it|its|just|like|make|many|me|might|more|
most|much|must|my|never|now|of|on|only|or|other|our|out|over|re|said|same|
see|should|since|so|some|still|such|take|than|that|the|their|them|then|
there|these|they|this|those|through|to|too|under|up|use|very|want|was|way|
we|well|were|what|when|where|which|while|who|will|with|would|you|your|a|b|
c|d|e|f|g|h|i|j|k|l|m|n|o|p|q|r|s|t|u|v|w|x|y|z)\b)"

        Dim spacePattern As String = "\s{2,}"

        'strip out the noise words

        Dim strOutput As String = RegularExpressions.Regex.Replace(str, noiseWordPattern, String.Empty, RegexOptions.IgnoreCase)

        'now remove redundant spaces

        strOutput = RegularExpressions.Regex.Replace(strOutput, spacePattern, " ").Trim

        Return strOutput


End Function

The point is in sql i want to do the following to exclude certain records that contain certain words such as:

declare @str varChar(1000)
set @str = 'AND NOT "developer" AND NOT "asp"'


Select *
from myTable
where Contains(myField, @str)

so this would give me records that do not contain developer or asp

However it seems that i have to have somthing that is in the field as my first word - but using a space is no use since it will be removed by sql server.

If i append a word to the end of the fields content such as "abracadara" and then search for

set @str = '"abracadara" AND NOT "developer" AND NOT "asp"'

this would work i asssume but its a bit of kludge! Anyone with a better solution?

cheers

firthy

 

[simpelli]

I did find a solution to the problem. To refresh, the problem I had was that I wanted a way to return all records AND NOT records with specific value using a full text search. Full Text searching does not allow you to only exclude values - there always has to be "find something with this criteria" AND "without this other criteria"

What I ended up doing when I built the criteria string from the values a user entered, if the only condition was to exclude something, I built the string to read something like: "WHERE CONTAINS(<field_name>,"a*") AND NOT @searchValue"

Even thought "a" is in the noise words list and is ignored by FT search, it seems to trick SQL into giving up everything & then filtering out what the user wanted to exclude.

I did not worry too much about returning inaccurate results (missing things that start with b, c, etc) because I'm limiting the returned records to 101 (And searching 5 different fields in 5 different tables for the value returns a lot of rows). If that number gets hit, I prompt the user: "Over 100 results were returned. Please refine your search". That forces them to filter first then exclude.

Of course the easiest solution would be to prompt the user to include the other values first & not allow them to exclude only in their search, but once I got rolling this way, I wanted to make it work. (some of you will probably understand...)

Thanks to everyone who helped me look into this.

 

[tbhutada]

Hello simpelli,

Can you pls help me in 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)
EXACT PHRASE - (Just like it sounds)
EXCLUDING WORDS - Words the search should ignore.


I am new to full text search and want to implement something similar.I needthe SP asap.

 

[simpelli]

Sure, here is the string builder portion of my SP:

declare
@searchCriteria nvarchar(1000), -- the final search string
@searchBasic nvarchar(1000),-- Basic search field- accept comma separated list - used on basic search page
@searchAll nvarchar(1000),-- Advanced search ALL WORDS-accept comma separated list (actually the same as basic if using advanced search)
@searchPhrase nvarchar(4000),-- Advanced search EXACT PHRASE - any single phrase (example) HI-TORQUE IRON
@searchNot nvarchar(4000)-- Advanced search EXCLUDE WORDS- word to filter out or ignore  Limit to 1 word 

--VALUES FOR TESTING
set @searchBasic = '14012-9, G08945, C08020MB, water'
set @searchAll =  'Screw, Tank, Needle'
set @searchPhrase= 'HI-TORQUE IRON'
set @searchNot = 'Welder'

--build the search criteria string
if @searchBasic is null begin
	if @searchAll is null begin
		if @searchPhrase is null begin
			if @searchNot is null begin--nothing to search for
				return	  
			end else begin
				set @searchCriteria='ISABOUT("a*"'+' AND NOT "'+@searchNot+'")'
			end
		end else begin
			set @searchCriteria='ISABOUT("'+@searchPhrase+'")'
			if @searchNot is not null set @searchCriteria=@searchCriteria +' AND NOT "'+@searchNot+'"'
		end 
	end else begin
		set @searchCriteria = '("'+ replace(replace(@searchAll,', ','" OR "'),',','" OR "')+ '"'--this changes the comma in the list to " OR "
		if @searchPhrase is not null begin
			set @searchCriteria = @searchCriteria + ' OR "'+@searchPhrase+'"'
		end
		if @searchNot is not null begin
			set @searchCriteria = @searchCriteria +') AND NOT "'+@searchNot+'"'
		end else begin
			set @searchCriteria = @searchCriteria + ')'
		end
	end
end else begin
	set @searchCriteria = '("'+ replace(replace(@searchBasic,', ','" OR "'),',','" OR "')+ '"'
	if @searchAll is not null begin
		set @searchCriteria = @searchCriteria + ' OR "'+ replace(replace(@searchAll,', ','" OR "'),',','" OR "')+ '"'--this changes the comma in the list to " OR "
	end
	if @searchPhrase is not null begin
		set @searchCriteria = @searchCriteria + ' OR "'+@searchPhrase+'"'
	end
	if @searchNot is not null begin
		set @searchCriteria = @searchCriteria +') AND NOT "'+@searchNot+'"'
	end else begin
		set @searchCriteria = @searchCriteria + ')'
	end
end

print @searchCriteria

/*
the output from passing the above should look like:
("14012-9" OR "G08945" OR "C08020MB" OR "water" OR "Screw" OR "Tank" OR "Needle" OR "HI-TORQUE IRON") AND NOT "Welder"

Since Part Numbers and Descriptions are are included in the criteria, you can search both fields with the same search criteria using Full Text Search:

Select * from Parts 
where CONTAINS(PartNumber,@searchCriteria) or CONTAINS(Description,@searchCriteria)

You could also search across multiple tables.  For example, if you are allowing Manufacturer Name in your search process, you could do something like:
Select Parts.PartNumber,Parts.Description 
from Parts inner join Manufacturer on Manufacturer.ManufacturerID = Parts.ManufacturerID 
where CONTAINS(Parts.PartNumber,@searchCriteria) or CONTAINS(Parts.Description,@searchCriteria) or CONTAINS(Manufacturer.Name,@searchCriteria)
*/

This is a bit long, so what I actually ended up doing is moving the string builder to the business object code in my application and just pass the final search string to the SP as the only parameter. Hope this helps!