Searching for a phrase using CONTAINS

I am wanting to use the CONTAINS predicate to search for "Class 3" as a phrase.

I have tried WHERE CONTAINS(FText,'"Class 3"') but I get all records with just "class" and all records with just "3" as well.

What is the correct syntax to retrieve "Class 3" (with both words next to each other separated by a space)?
LVL 3
jdthedjAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

HuaMin ChenProblem resolverCommented:
You can use this instead
where instr(ftext,'Class 3')...

Open in new window

0
chaauCommented:
It could be that "3" is in the stopwords list. Please check the link above for more information. To view the stoplist use this query:
SELECT * FROM sys.fulltext_stopwords

Open in new window

0
jdthedjAuthor Commented:
Thanks for the replies.  

@chaau "3" is not in the stopwords list.  

@HuaMinChen I could use instr and I could use like, but I really want to use contains, rather than changing the programming for each search.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

PortletPaulfreelancerCommented:
Do either of these get results?
declare @c as varchar(7) = 'Class 3'
WHERE CONTAINS(FText,@c)
---
WHERE CONTAINS(FText, 'NEAR((Class,3), 1, TRUE)')

Open in new window

0
jdthedjAuthor Commented:
@PortletPaul  The first one doesn't work -

the 2nd one gives the error "Syntax error near '(' in the full-text search condition 'NEAR((Class,3), 1, TRUE)'."
0
PortletPaulfreelancerCommented:
oh! sorry! That's sql 2012+ syntax

2008 R2: http://msdn.microsoft.com/en-AU/library/ms187787(v=sql.105).aspx
2012: http://msdn.microsoft.com/en-AU/library/ms187787(v=sql.110).aspx

WHERE CONTAINS(FText, 'Class NEAR 3')

but this isn't really what I was hoping for, I'm out of ideas at this point.
0
jdthedjAuthor Commented:
Thanks Paul

I'm in 2008 R2, and from what I am reading NEAR doesn't cater for proximity.  I have also discovered that even though the stopword "3" is not in the list "three" is and it appears it is being recognised as "3".  When I remove "three" from the stopwords the search works, but strangely one search result came up showing "class 1,2".  It must be adding 1+2 to get three!!!
0
PortletPaulfreelancerCommented:
"NEAR doesn't cater for proximity" that's my understanding also, until 2012, where you can specify the proximity range

but seems you have it worked out which is good.
0
chaauCommented:
No, it is not that it is adding 1 and 2. It just ignores any stopwords and punctuation in between "class" and "3" I assume 1 is also in your stopwords list.
Anyway, it is not possible to do an exact match with the CONTAINS. If you really need the exact match you must use LIKE. Here, read it from MSDN:
Punctuation is ignored. Therefore, CONTAINS(testing, "computer failure") matches a row with the value, "Where is my computer? Failure to find it would be expensive."
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chaauCommented:
Thanks Vikas. However, please add more comments to your post. Some moderators may treat it as a blind link post and delete. You can test how CONTAINS works by analysing this query:
SELECT special_term, display_term
FROM sys.dm_fts_parser
  (' "Class 3" ', 1033, 5, 0)

Open in new window

It will tell you what words are noise, and what words are exact match
0
jdthedjAuthor Commented:
Thanks everyone for the replies.  

I'm going to have to go back to the drawing board to code a search page for the users which will have a mixture of LIKE and CONTAINS.  What a headache!  Hopefully eventually the boss will upgrade to 2012+ and I can use proximity with NEAR.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.