SQL Server full-text search

Question

I want to use the full-text index to search word:

How can I use 'contain' or 'freetext' to get the same result as "like '%dd%' "? If I use contain(table,'data'), it only finds "....the data is ...." How can I use contain search 'dat' to find data?

Answer 1

While LIKE is searching for character patterns within a string, SQL Server's full text search is word based. The index is built using word breakers and stemmers to tokenize the text into words. So, you can search for whole words or perform prefix searches (so that CONTAINS (YourColumn, ' "dat*" ' ) would find the word data in your example), but you cannot replicate the behavior of like '%dd%' with full text.