I have a Full-Text Catalog that is populated by various columns in a few different tables. I have been able to create a stored procedure that will search across all of the different full-text columns and return me the results.
My problem is, if the someone searches for
hello world
then to my understanding I want to use FREETEXTTABLE to return my results (I actually get 0 results if I use CONTAINSTABLE)
If someone searches for
"hello world"
then I want to use CONTAINSTABLE because FREETEXTTABLE returns too many results.
And now the biggest problem would be, if someone searches for
"hello world" program
I would somehow need to use CONTAINSTABLE for the phrase and FREETEXTTABLE for 'program'. The SQL to accomplish something like this would probably be very ugly (if possible at all)
Can anyone give me any suggestions on this matter? I'm trying to create a google like search on a database which contains text and files (as BLOBs).
Thanks in advanceI should clarify a bit more.
If someone searches for
"hello world"
then I want to use CONTAINSTABLE because FREETEXTTABLE returns too many results.
The problem isn't that FREETEXTTABLE returns too many results but rather the rankings that I get back aren't helpful. Say I have 2 completely different tables and I want to search across both of them. Table1 has rows that contain the phrase "hello world" while Table2 doesn't contain that specific phrase, but does contain the separate words 'hello' and 'world'. I run FREETEXTTABLE on both Table1 and Table2, Union the results, and then return the table to my web app (where other formatting occurs before the results are displayed to the user.) Unfortunately because of how the Ranks are calculated, the entries in Table2 have a higher Rank than the entries in Table1 (even though the exact phrase occurs in Table1)
I want the rows from Table1 to appear before Table2 in this case.
One solution that I have been playing with is to always use CONTAINSTABLE, use regular expressions to insert 'AND' where applicaple, and use the 'FORMSOF(INFLECTIONAL, @.searchStr)' option. Unfortunately this seems to break when someone searches for
hello world
because I would insert an 'AND' between the words and then try to find the inflectional forms of "hello AND world" which of course breaks.
Once again, and ideas/suggestions would be greatly appreciated|||I think I've found the solution
If someone wants to search for...
"hello world" program
Then the sql would look like this...
SELECT *
FROM CONTAINSTABLE(T_Table, *, 'FORMSOF(INFLECTIONAL, "hello world") AND FORMSOF(INFLECTIONAL, "program")')|||Thanks EvilMonkey - very helpful. =)|||
cool but can u give some details.
