I am trying to use a Full Text Search Query inside a stored procedure. However when i execute the following code i get no results even though there are rows in the table that match the searchword.
select * from dbo.DiagnosisCode where Contains(DiagnosisDescription, '"and"')
I am able to get results when i concatenate the "and" with a wild-character. What could be the reason why the query returns no rows for ''and''.
I think i figured it out, and is added as a noise word because of which the full text search is returning no results. i have modified the NoiseEng.txt file and excluded it from the file and repopulated my catalog.
The search works now with "and".