SQL Server Full Text Searching

Question

I am currently working on an application where we have a SQL Server database and I need to get a full text search working that allows us to search people's names.

Currently the user can enter a into a name field that searches 3 different varchar cols. First, Last, Middle names

So say I have 3 rows with the following info.

1 - Phillip - J - Fry

2 - Amy - NULL - Wong

3 - Leo - NULL - Wong

If the user enters a name such as 'Fry' it will return row 1. However if they enter Phillip Fry, or Fr, or Phil they get nothing.. and I don't understand why its doing this. If they search for Wong they get rows 2 and 3 if they search for Amy Wong they again get nothing.

Currently the query is using CONTAINSTABLE but I have switched that with FREETEXTTABLE, CONTAINS, and FREETEXT without any noticeable differences in the results. The table methods are be preferred because they return the same results but with ranking.

Here is the query.

....
@Name nvarchar(100),
....
--""s added to prevent crash if searching on more then one word.
DECLARE @SearchString varchar(100)
SET @SearchString = '"'+@Name+'"'
SELECT Per.Lastname, Per.Firstname, Per.MiddleName
FROM Person as Per
INNER JOIN CONTAINSTABLE(Person, (LastName, Firstname, MiddleName), @SearchString) 
AS KEYTBL
ON Per.Person_ID = KEYTBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEYTBL.RANK DESC;  
....

Any Ideas why this full text search is not working correctly?

Answer 1

FreeTextTable should work.

INNER JOIN FREETEXTTABLE(Person, (LastName, Firstname, MiddleName), @SearchString)

@SearchString should contain the values like 'Phillip Fry' (one long string containing all of the lookup strings separated by spaces).

If you would like to search for Fr or Phil, you should use asterisk: Phil* and Fr*

'Phil' is looking for exactly the word 'Phil'. 'Phil*' is looking for every word which is starting with 'Phil'

Answer 2

If you're just searching people's names, it might be in your best interest to not even use the full text index. Full text index makes sense when you have large text fields, but if you're mostly dealing with one word per field, I'm not sure how much extra you would get out of full text indexes. Waiting for the full text index to reindex itself before you can search for new records can be one of the many problems.

You could just make a query such as the following. Split your searchstring on spaces, and create a list of the search terms.

Select FirstName,MiddleName,LastName 
From person 
WHERE 
Firstname like @searchterm1 + '%'
or MiddleName like @searchterm1 + '%'
or LastName like @searchterm1 + '%'
or Firstname like @searchterm2 + '%'
etc....

Answer 3

Another approach could be to abstract the searching away from the individual fields.

In other words create a view on your data which turns all the split fields like firstname lastname into concatenated fields i.e. full_name

Then search on the view. This would likely make the search query simpler.

Answer 4

Thanks for the responses guys I finally was able to get it to work. With part of both Biri, and Kibbee's answers. I needed to add * to the string and break it up on spaces in order to work. So in the end I got

....
@Name nvarchar(100),
....
--""s added to prevent crash if searching on more then one word.
DECLARE @SearchString varchar(100)

--Added this line
SET @SearchString = REPLACE(@Name, ' ', '*" OR "*')
SET @SearchString = '"*'+@SearchString+'*"'

SELECT Per.Lastname, Per.Firstname, Per.MiddleName
FROM Person as Per
INNER JOIN CONTAINSTABLE(Person, (LastName, Firstname, MiddleName), @SearchString) 
AS KEYTBL
ON Per.Person_ID = KEYTBL.[KEY]
WHERE KEY_TBL.RANK > 2
ORDER BY KEYTBL.RANK DESC;  
....

There are more fields being searched upon I just simplified it for the question, sorry about that, I didn't think it would effect the answer. It actually searches a column that has a csv of nicknames and a notes column as well.

Thanks for the help.

BTW I couldn't figure out how to say this was the solution...

Answer 5

Cory: There is a link after every answer with green 'accept answer'.

Answer 6

You might want to check out Lucene.net as an alternative to Full Text.