I have an application in which a user can typ in a word or words and SQL Server finds the right rows.
I've used FREETEXT because I want it to be possible that the user can type multiple words to search on (with CONTAINS I have to convert that string to 'Word1 OR Word2').
But I just found out that with FREETEXT, if I search for example for 'Projectnummer' and I type 'Project', it will not find it. I cannot use a * in FREETEXT or an % to achieve the desired result.
How can I use FREETEXT so that I'm able to find words on a partial criterium and still search for multiple words
To be honest, Full Text is just as bad for wildcard searches and partial lookup as LIKE. The only good part about it is that it splits "phrases" into words, which gives the elusion that you're doing some form of mid-string lookup more quickly.
Please see the following for a bit more information on that which I speak.
The only way that you could really get some speed out of apparent mid or partial string lookups is to build a synonym table and I have to tell you that's not my idea of entertainment.
probably not relevant, but we have a table of "words". We split out individual works and stuff them in that table; we have a list of Synonyms - I found one somewhere on the InterWeb - and store the root-word, and ignore any noise words.
We created the table with columns for "TableAndColumnID", "RowID", "Word" and "Soundex". Originally intended for Product Descriptions (for a predictive search formfield) we have widened it to use it on all address lines and so on, as it makes finding a matching row, in various tables, useful.
Based on the original Product Search requirement a User can added additional words - e.g. for competitor part numbers, and synonyms that users might search on, also words that should not find that product (but which are included in the description) can be marked as excluded.
A trigger maintains the split-word-list for Tables that make use of the Word List Table