<p>I'm 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.</p>

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

<p>So say I have 3 rows with the following info.</p>

<p>1 - Phillip - J - Fry</p>

<p>2 - Amy - NULL - Wong</p>

<p>3 - Leo - NULL - Wong</p>

<p>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.</p>

<p>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.</p>

<p>Here is the query.</p>

<pre><code>....
@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 &gt; 2
ORDER BY KEYTBL.RANK DESC;  
....
</code></pre>

<p>Any Ideas...? Why this full text search is not working correctly ?</p>

[{'answer_id': 18072, 'author': 'Biri', 'author_id': 968, 'author_profile': 'https://Stackoverflow.com/users/968', 'pm_score': 2, 'selected': False, 'text': "<p>FreeTextTable should work.</p>\n\n<pre><code>INNER JOIN FREETEXTTABLE(Person, (LastName, Firstname, MiddleName), @SearchString) \n</code></pre>\n\n<p>@SearchString should contain the values like 'Phillip Fry' (one long string containing all of the lookup strings separated by spaces).</p>\n\n<p>If you would like to search for Fr or Phil, you should use asterisk: Phil* and Fr*</p>\n\n<p>'Phil' is looking for exactly the word 'Phil'. 'Phil*' is looking for every word which is starting with 'Phil'</p>\n"}, {'answer_id': 18096, 'author': 'Kibbee', 'author_id': 1862, 'author_profile': 'https://Stackoverflow.com/users/1862', 'pm_score': 3, 'selected': False, 'text': "<p>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.</p>\n\n<p>You could just make a query such as the following.  Split your searchstring on spaces, and create a list of the search terms.</p>\n\n<pre>\nSelect FirstName,MiddleName,LastName \nFrom person \nWHERE \nFirstname like @searchterm1 + '%'\nor MiddleName like @searchterm1 + '%'\nor LastName like @searchterm1 + '%'\nor Firstname like @searchterm2 + '%'\netc....\n</pre>\n"}, {'answer_id': 18144, 'author': 'Tim', 'author_id': 1970, 'author_profile': 'https://Stackoverflow.com/users/1970', 'pm_score': 2, 'selected': False, 'text': '<p>Another approach could be to abstract the searching away from the individual fields.</p>\n\n<p>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</p>\n\n<p>Then search on the view. This would likely make the search query simpler. </p>\n'}, {'answer_id': 18226, 'author': 'corymathews', 'author_id': 1925, 'author_profile': 'https://Stackoverflow.com/users/1925', 'pm_score': 3, 'selected': True, 'text': '<p>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</p>\n\n<pre><code>....\n@Name nvarchar(100),\n....\n--""s added to prevent crash if searching on more then one word.\nDECLARE @SearchString varchar(100)\n\n--Added this line\nSET @SearchString = REPLACE(@Name, \' \', \'*" OR "*\')\nSET @SearchString = \'"*\'+@SearchString+\'*"\'\n\nSELECT Per.Lastname, Per.Firstname, Per.MiddleName\nFROM Person as Per\nINNER JOIN CONTAINSTABLE(Person, (LastName, Firstname, MiddleName), @SearchString) \nAS KEYTBL\nON Per.Person_ID = KEYTBL.[KEY]\nWHERE KEY_TBL.RANK &gt; 2\nORDER BY KEYTBL.RANK DESC;  \n....\n</code></pre>\n\n<p>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.</p>\n\n<p>Thanks for the help.</p>\n'}, {'answer_id': 18317, 'author': 'Brett Veenstra', 'author_id': 307, 'author_profile': 'https://Stackoverflow.com/users/307', 'pm_score': 2, 'selected': False, 'text': '<p>You might want to check out <a href="http://incubator.apache.org/lucene.net/" rel="nofollow noreferrer">Lucene.net</a> as an alternative to Full Text.</p>\n'}]
['https://Stackoverflow.com/questions/17056', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/1925/']