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?

Adapter

For a start, the spacing on the link you provided is different to your example code, have you tried: CONTAINS(*, ' "test" ') – Caespitose

Isn't this what Contains() is supposed to do? 'test' is surely contained within 'numerictest'. I would guess that exact means that it seaches for a specific term, rather than term + inflections of the term, like FREETEXT() does. – Vindictive

CONTAINS(*, ' "test" ') results the same as the first two. I would use LIKE for the exact search but it takes to long. The only solution at the moment is two search with CONTAINS(*, ' "test" ') and than filter the results in code again... Not very beautiful... – Adapter

What do you mean by exact search? Do you mean that it should only match 'test'? If so, then you can use the equals operator (= 'test') rather than LIKE (which is slower due to pattern matching). If you decide to use the contains() approach, wrap it inside a CTE (Common table expression), and do your query against that. – Vindictive

@MariaGustavson What you are seeing is not the way that CONTAINS is supposed to work. If you search on "test" then it should not match "numerictest". So there definitely is a problem. Did you configure a custom word breaker? Are there multiple full-text indexed columns and maybe one of them does contain "test"? Have you tried repopulating the full-text index? – Arabela

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.

Stove

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.

Leacock