We came across an issue using FullText Indexes and SQL 2008 R2, not sure if it's by design or I am missing something. When we search using a fulltext index, I noticed SQL appears to trim leading and trailing spaces for words, I also noticed it appears to do a start with. Meaning, if I am searching for last name that has ders, it won't return Anderson. But If I search for Ander, it will return Anderson. We have tried using CONTAINS and FREETEXT. Is there a way to for it to behave more like a like (%ders%) instead the way it is behaving? Thanks.
Like our expert ypercube mentioned, you can use
LIKE if you know the phrase to be searched.
Otherwise you can also use something called
CHARINDEX which I personally find a very useful command in SQL. I'll give you a small example. Suppose you have a keyword and you want to search that keyword in a column or a group of column, you can do it pretty easily.
DECLARE @keyword VARCHAR(20) = 'ders'; SELECT * FROM YourTableName WHERE CHARINDEX (@keyword, [LastName]) > -1;
This works on variables too very well. Let me know if this resolves your query.
Now if you had to apply the same search on multiple columns, you could write:
SELECT * FROM YourTableName WHERE CHARINDEX (@keyword, [LastName] + '|' + [FirstName] + '|' + [MiddleName]) > -1;