SQL Full-Text Indexing Issue

UPDATE: I have figured out a way using a form of dynamic sql to fix this problem, thanks anyway for any help.

Hi, there is something that I need to accomplish with the use of Full-Text Indexing. This is it:

The fact of the matter is when I run a query (with a stored procedure) that looks like (with a parameter (@name) that was obviously defined above (not shown here), this parameter is sent to the stored procedure by an asp.net page, from user input):

SELECT

Name

FROMdbo.UsersTable

WHERE

F开发者_运维技巧REETEXT(Name, @name)

Well, the fact of the matter is that a query like this will return values if, say the parameter @name's value is Joe, and say, there are 10 records of names with Joe in them, but if @name's value is just Jo, then it returns nothing, and this is the problem. Say that there are other records in this table that have Jo in them, like for example, Jole, or John.

So the real question is, how do I get it to return values that are not full words, or phrases, but just from part of the word/phrase (like I said above)? Like FREETEXT(Name, @name*), which is not allowed to be used as a query, but, you get the idea.

Is there a way to accomplish this? I'm sure there must be, I need to figure this out.

Thanks for any help.

Full text searching is really supposed to be used as a natural language search based on whole words, however you can use it to look for words suffixes (not substrings) using CONTAINS with a pattern in the form "Jo*"

SELECT Name FROM dbo.UsersTable WHERE CONTAINS(Name, '"' + @NAME + '*"')