Optional columns in Full-Text Search

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optional columns in Full-Text Search

Author

Topic

fizgig
Starting Member

34 Posts

Posted - 2002-06-10 : 10:32:24

I have a enabled full-text search on a table; columns name, email and organisation.

I want users to specifify which columns to search in. For example: a search can look like this: search in [name] AND [organisation] for
"pizza company". But the user can also select to just search [name] or all the columns.

Can this be done without dynamically building a SQL string ? If so, how?

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-10 : 11:02:13

I've never used Full-Text Indexes before, but optional search criteria can be handled pretty easily if you pass a null for a non-existant search variable . . .


...
where
    coalesce([name],'NULL') like '%' + coalesce(@name,[name],'NULL') + '%' and
    coalesce([organisation],'NULL') like '%' + coalesce(@organisation,[organisation],'NULL') + '%' and
    coalesce......

<O>

fizgig
Starting Member

34 Posts

Posted - 2002-06-10 : 11:49:16

Problem is that with full-text search queries this isn't an option because I use the ContainsTable method.

As you can see the ContainsTable returns a custom table with RANK information (relativ relevancy of the returned row) :

Select
id,
[KEY],
RANK,
name = (COALESCE(firstname, ' ') + ' ' + COALESCE(lastname, ' ')),
email
FROM
ContainsTable(subscriber, *, @Zoekopdracht,300 ) S,
subscriber SB
WHERE
SB.id = S.[KEY] AND Newsletter = @Newsletter
ORDER BY
Rank Desc, name DESC

To make it harder, the ContainsTable method only accepts * (=all columns) or just 1 column name. You can fix that by using inner joins. (see example below, but with different table/columns) But how can I make those inner joins (and rank calulations) optional?

select 2*pk.rank, 3* pk1.rank, 1.5*pk2.rank, Title, uniqueID from TableName
inner join containstable (TableName, Title, 'Farshad') as pk on
TableName.UniqueId=pk.[key]
inner join containstable (TableName, URL, 'Farshad') as pk1 on
TableName.UniqueId=pk1.[key]
inner join containstable (TableName, Description, 'Farshad') as pk2 on
TableName.UniqueId=pk2.[key]

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-10 : 12:37:16

yikes...like I said, I've never worked with full-text search...

I'm looking at books on line at the topic "Using the CONTAINSTABLE and FREETEXTTABLE Rowset-valued Functions". At the bottom there is a sample proc called 'freetext_rank_proc'. It seems to build the string dynamically, pointing to me, at least, that dynamic sql is your best bet . . .

Sorry for the unedumacated response above . . .

<O>

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources