I try to implement a search-mechanism with "CONTAINS()" on a SQL Server 2014.
I've read here https://technet.microsoft.com/en-us/library/ms142538%28v=sql.105%29.aspx and in the book "Pro Full-Text Search in SQL Server 2008" that I need to use double quotes to search an exact phrase.
But e.q. if I use this CONTAINS(*, '"test"')
I receive results containing words like "numerictest" also. If I try CONTAINS(*, '" test "')
it is the same. I've noticed, that there are less results as if I would search with CONTAINS(*, '*test*')
for a prefix, sufix search, so there is definitely a delta between the searches.
I didn't expect the "numerictest" in the first statement. Is there an explanation for this behaviour?
Best Answer
I have been wracking my brain about a very similar problem and I recently found the solution.
In my case I was searching full text fields for "@username" but using
CONTAINS(body, "@username")
returned just "username" as well. I wanted it to strictly match with the @ sign.I could use
LIKE "%@username%"
but the query took over a minute which was unacceptable so I kept looking.With the help of some people in a chat room they suggested using both CONTAINS and LIKE. So:
SELECT TOP 25 * FROM table WHERE
CONTAINS(body, "@username") AND body LIKE "%@username%";
this worked perfectly for me because the contains pulls both username and @username records and then the LIKE filters out the ones with the @ sign. Queries take 2-3 seconds now.
I know this is an old question but I came across it in my searching so having the answer I thought I would post it. I hope this helps.