SQL 2005 Full-Text-Search: How to default all user search words to a CONTAINS NEAR type FTS search.

Question

I have an asp.net web page with a simple search text box that returns matching rows from a MSSQL 2005 database. It is currently using a LIKE statement to bring back matches, but forces the user to to type an exact phrase. Users want a more Google search type experience and so I have decided to set up and index the needed tables with FTS.

I would like the users search word or words to be used in a CONTAINS search with NEAR separating each word they type into the text box. I'm a new developer and do not know how to do this, or if there is already a built in function to cover this.

So for example, if a user types "Sawyer Tom" into the search box the query should function like this:

SELECT BookID, BookTitle FROM tblBooks WHERE CONTAINS(BookTitle, 'Sawyer NEAR Tom')

And return:

12032 The Adventures of Tom Sawyer

Currently using the like statement I have now the user search would find no matches.

If the user types simply Sawyer the query should function simply like this:

SELECT BookID, BookTitle FROM tblBooks WHERE CONTAINS(BookTitle, 'Sawyer')

Returning:

12032 The Adventures of Tom Sawyer 72192 Roy Crane's Buz Sawyer: The War in the Pacific (Vol. 1)

My current code just plugs in the search string into the query like this:

SELECT BookID, BookTitle FROM tblBooks WHERE CONTAINS(BookTitle, @Search)

Which obviously doesn't work. How can I separate each word automatically with NEAR?

Thanks so much in advance for any help you can provide!

-David

Answer 1

First, you would need to split your input @Search variable into words.

Here's a blog post with a UDF that will accomplish that:

T-SQL to Split a varchar into Words

Next, you would look through each word returned, and concatenate each word with a NEAR query.

Something like this:

declare @words as TABLE(wordNum int identity primary key, word nvarchar(max))

insert into @words select [value] from  dbo.SplitWords('my dog has fleas')

declare @wordCount int
select @wordCount = COUNT(*) from @words

declare @searchString nvarchar(max)
set @searchString = ''

declare @thisWord nvarchar(max)
set @thisWord = ''

declare @i int
set @i = 1
WHILE @i <= @wordCount
BEGIN
    select @thisWord = word from @words where wordNum = @i
    if @i = @wordCount
        select @searchString = @searchString + @thisWord
    else
        select @searchString = @searchString + @thisWord + ' NEAR '
    SET @i = @i + 1
END