Full Text Search with Empty Search String

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