SQL Contains exact phrase

Question

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?

Answer 1

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.

Answer 2

Contains(*,'"test"') will only match full words of "test" as you expect.

Contains(*,'" test "') same as above

Contains(*,'"*test*"') will actually do a PREFIX ONLY search, basically strips out any special characters at the start of word and only uses the 2nd *. You cannot do POSTFIX searches using full text search.

My concern lies with the Contains(*) part, this will search for any full text cataloged items in that entire row. Without seeing the data it is hard to tell but my guess is that another column in that row you think is bad is actually matching on "test" somewhere.