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

Full Text Search on Two columns

How do I use FREETEXTTABLE on two columns in a table but rank by only one
column.
For example I have two columns News_Summary,News_Content
In this case the user can specify during search if he wants to search
News_Summary or the News_Content or Both.There also other columns in the
tables.
In any case I want to rank only by News_Content.
What is the easiest way to accomplish this.
This is the Query I am using right now,How do I add one more column to this.
I dont want to use mutlple stored procedures.
SELECT *
FROM news AS FT_TBL INNER JOIN
FREETEXTTABLE(news,news_summary,'FORMSOF(INFLECTIO NAL, "Report")') AS KEY_TBL
ON FT_TBL.news_id= KEY_TBL.[KEY]
WHERE(
expire_dtm>getdate()
)
"Bruce" wrote:

> How do I use FREETEXTTABLE on two columns in a table but rank by only one
> column.
> For example I have two columns News_Summary,News_Content
> In this case the user can specify during search if he wants to search
> News_Summary or the News_Content or Both.There also other columns in the
> tables.
> In any case I want to rank only by News_Content.
> What is the easiest way to accomplish this.
>
|||Bruce,
You can use the following examples of multiple FREETEXTTABLE for multiple
columns in the same table (Employees). Note the "AND" condition between the
Primary Keys, you can also change to an "OR" condition.
-- multiple columns from one FT enable table using FREETEXTTABLE in the
Northwind database
use Northwind
go
SELECT e.LastName, e.FirstName, e.Title, e.Notes
from Employees AS e,
freetexttable(Employees, Notes, 'BA') as A,
freetexttable(Employees, Title, 'Sales') as B
where
A.[KEY] = e.EmployeeID AND
B.[KEY] = e.EmployeeID
use pubs
go
SELECT pub_id, pr_info FROM pub_info
WHERE FREETEXT(pr_info, ' "publish*" ') AND FREETEXT(pub_id, ' "0736*" ')
Yes, you can search as many columns as necessary, but be aware that each FTS
predicate (CONTAINS* or FREETEXT*) is a "round-trip" to the FT Catalog and
depending upon the size of your FT Catalog (based upon the number of rows in
your FT enable table as well as the number of non-noise unique words), your
FTS query performance may be affected.
Regards,
John
"Bruce" <Bruce@.discussions.microsoft.com> wrote in message
news:BD072BA9-6A90-4C72-B61F-35DC691EC044@.microsoft.com...
> This is the Query I am using right now,How do I add one more column to
this.
> I dont want to use mutlple stored procedures.
> SELECT *
> FROM news AS FT_TBL INNER JOIN
> FREETEXTTABLE(news,news_summary,'FORMSOF(INFLECTIO NAL, "Report")') AS
KEY_TBL[vbcol=seagreen]
> ON FT_TBL.news_id= KEY_TBL.[KEY]
> WHERE(
> expire_dtm>getdate()
> )
> "Bruce" wrote:
one[vbcol=seagreen]
sql