0

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
    If you want LIKE functionality, why don't you use LIKE? – ypercubeᵀᴹ May 6 '16 at 3:24
  • I thought to take advantage of the FullText index I had to use contains or freetext? We used like before and our wildcard searches took 30 seconds, when we enabled FullText index it went down to 5 seconds. – Jason B. May 7 '16 at 1:59
0

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;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.