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.
1 Answer
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;
LIKE
functionality, why don't you useLIKE
?