June 1, 2008 at 12:16 am
Hello guys,
Need help with FREETEXT function.
Say user search NORWEGIAN WOOD. Then books titled NORWEGIAN and books titled WOOD appear. So it's like an OR in effect here. How to make such that only books containing NORWEGIAN AND WOOD appear?
Thanks in advance for the help.
M
June 2, 2008 at 7:32 am
Here's the help on the "freetext string" that goes into the FREETEXT function, which explains about quoted strings:
freetext_string
Is text to search for in the column_name. Any text, including words, phrases or sentences, can be entered. Matches are generated if any term or the forms of any term is found in the full-text index.
Unlike in the CONTAINS search condition where AND is a keyword, when used in freetext_string the word 'and' is considered a noise word and will be discarded.
Use of WEIGHT, FORMSOF, wildcards, NEAR and other syntax is not allowed. freetext_string is wordbroken, stemmed, and passed through the thesaurus. If freetext_string is enclosed in double quotation marks, a phrase match is instead performed; stemming and thesaurus are not performed.
It would appear you'll need to educate the users that double quotations marks would be needed to achieve the desired goal.
Steve
(aka smunson)
:):):)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
June 3, 2008 at 6:57 am
I strongly suggest that you use CONTAINS vs. FREETEXT in order to have better control over the search syntax.
December 16, 2008 at 8:23 am
You need to separate the terms (words, or phrases as you like), enclose them in double quotes ("), and then join them back using "AND". Then pass the new string as a new keyword.
Code example in VB.Net:
Private _keywords As String = ""
Public Property Keywords() As String
Get
Return _keywords
End Get
Set(ByVal value As String)
_keywords = SplitKeyWord(value)
End Set
End Property
Private Function SplitKeyWord(ByVal value As String) As String
Dim result As String = ""
For Each part As String In value.Split(" ")
If (Not (String.IsNullOrEmpty(part.Trim()))) Then
result += """" & part & """ AND "
End If
Next
If (result.Length > 0) Then
result = result.Substring(0, result.LastIndexOf("AND"))
End If
Return result
End Function
Now, if you set the Keywords property as "vital safety components", the Getter function will return the following string:
""vital" AND "safety" AND "components""
Finally, you pass the new keyword property to your SP,
Alter Procedure Foo @keywords nvarchar(max)
as
begin
.
.
.
select * from mainTable inner join
FreeTextTable(mainTable , ([title], description), @keywords) as ft on main.id = ft.
.
.
.
end
January 29, 2009 at 10:14 am
Here is the above function in c#, thanks. I did everything like you suggested though and I am still getting matches on single words in a phrase. Like a search of star trek is giving me results with only star in them. I switched it to a ContainsTable and got the results I want. Are you sure freetext can be forced to do an "and"?
private string _keywords = "";
public string Keywords
{
get { return _keywords; }
set { _keywords = SplitKeyWord(value); }
}
private string SplitKeyWord(string value)
{
string result = "";
foreach (string part in value.Split(' '))
{
if ((!(string.IsNullOrEmpty(part.Trim()))))
{
result += "\"" + part + "\" AND ";
}
}
if ((result.Length > 0))
{
result = result.Substring(0, result.LastIndexOf("AND"));
}
return result;
}
January 29, 2009 at 10:53 am
I also found adding a star to the end of the term and using the technique above with containstable is the result most people would want generally.
here is the changed line in the set property
result += "\"" + part + "*\" AND ";
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply