[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Searching for a phrase using CONTAINS

Posted on 2014-08-11
12
Medium Priority
?
143 Views
Last Modified: 2014-08-22
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)?
0
Comment
Question by:jdthedj
  • 4
  • 3
  • 3
  • +1
11 Comments
 
LVL 11

Expert Comment

by:HuaMinChen
ID: 40254834
You can use this instead
where instr(ftext,'Class 3')...

Open in new window

0
 
LVL 25

Expert Comment

by:chaau
ID: 40254842
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
 
LVL 3

Author Comment

by:jdthedj
ID: 40254852
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
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
LVL 49

Expert Comment

by:PortletPaul
ID: 40254870
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
 
LVL 3

Author Comment

by:jdthedj
ID: 40254890
@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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40254925
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
 
LVL 3

Author Comment

by:jdthedj
ID: 40254930
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
 
LVL 49

Expert Comment

by:PortletPaul
ID: 40254946
"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
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40254947
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
 
LVL 25

Expert Comment

by:chaau
ID: 40254958
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
 
LVL 3

Author Closing Comment

by:jdthedj
ID: 40256792
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

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question