FullText Search

Question

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.

Answer 1

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;