Hi,
I have implemented full text search in one of my applications and i am using FREETEXTTABLE to perform the search. Actually i wanna to show all rows from the table whenever search string is empty,
Although i am currently using Dynamic sql to do this task, but i just wanna to know best practices to handle the situation ? Is there any way that i could use WildCard to return all rows?
Please suggest me a better way of doing it.
Thanks
/* You will need to adapt these examples */
/*This is the general pattern for returning the unfiltered record set
when the parameter is NULL. */
WHERE MyColumn = ISNULL(@.myVariable, MyColumn)
/*The following code snippets are taken from a production sproc that performs a complex search that includes a FULL-TEXT search. The input parameter is @.Keywords, it's DEFAULT = '' and the WHERE clause works if @.Keywords is NULL or not.
*/
--First
DECLARE @.KeywordNull1int
DECLARE @.KeywordNull2int
--second
IF @.Keywords = ''
BEGIN
SET @.KeywordNull1 = 1
SET @.KeywordNull2 = 1
END
ELSE IF @.Keywords <> ''
BEGIN
SET @.KeywordNull1 = 1
SET @.KeywordNull2 = 2
END
--third
--this is part of a complex WHERE clause
AND((CONTAINS(f.*,@.Keywords) OR (@.KeywordNull1 = @.KeywordNull2)) OR (CONTAINS(fl.*,@.Keywords) OR (@.KeywordNull1 = @.KeywordNull2)) OR (CONTAINS(FFT.*,@.Keywords) OR (@.KeywordNull1 = @.KeywordNull2)))
ORDER BY fl.FolderPath,[FileName],FileExtension
|||Hi,
Thanks very much for your reply it will be very helpful for me in future projects, but i am using FREETEXTTABLE for search purposes not CONTAINS,
i am even not sure which one is better, Do you have any idea which one actually is ?
|||Since I have no idea what your SELECT statement that contains FREETEXTTABLE looks like or what you are trying to do I have no way to answer your question.|||
RE general question of comparison of CONTAINS/CONTAINSTABLE to FREETEXT/FREETEXTTABLE - none is better or worse, they are intended for different scenarios.
1) CONTAINS and CONTAINSTABLE have a simpler ranking function (called TF/IDF in Information Retrieval) and support a mini-query language that allows precise specification of the user's intent - is supports operators like AND, OR, NEAR, FORMSOF(INFLECTIONAL,...)/FORMSOF(THESAURUS,...) etc.
2) FREETEXT and FREETEXTTABLE are have more complex ranking (OKAPI BM 25 in IR terms) and are intended to more closely resemble Web search experience over full-text-indexed data in your database.
You may want to start with http://msdn2.microsoft.com/en-us/library/ms142494.aspx or more generally, at http://msdn2.microsoft.com/en-us/library/ms142547.aspx to get more details.
Best regards,
|||Thanks Denistch , it really helped me!!!|||Hi Denistch,
Problem is still the same, let say if i use query select tblID from CONTAINSTABLE(Table1,*,'') as keytable where ....
so whatever the query is, if search string is empty, how can i use wildcard to return all the rows from Table1. e.g if i use something like CONTAINSTABLE(Table1,*,"a*") it will return all the matching words starting with 'a', but i dont want just 'a', i want all rows!!!
Any help will be highly appreciated .
I know i can use CONTAINS or LIKE, but i have got very long dynamic query, if change to CONTAINS it does'nt give me rank, so i would be changing the whole query just for one scenario....which i dont wanna do!
Thanx!
sql