SQL Contains exact phrase
contains, full-text-search, sql-server, sql-server-2014
Solution
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.
Problem
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?