Take the 2-minute tour ×
SharePoint Stack Exchange is a question and answer site for SharePoint enthusiasts. It's 100% free, no registration required.

I am using the CONTAINS predicate in a full text SQL query in SP2010 Standard. Yes, I'd rather do a Keyword Query, but it doesn't appear to be able to handle what I need.

I have learned things like changing a space to a "+" in all quoted queries, changing a space between words to " OR ", etc.

I have also found that the following characters seem to mess up a query if put in CONTAINS:

~!()&[]|',

My current fix is to simply remove these from the query. This is probably OK for all but the single quote. If a user wants to find "can't" or "O'Reilly" they really need to be able to. I must use a CONTAINS because I have to limit this to certain fields (metadata properties).

I have tried escaping it with \', \', \\', '', , and a few others I can't remember but none have worked for me.

Surprisingly the documentation on this doesn't mention escaping any characters.

Any ideas?

share|improve this question

1 Answer 1

The FREETEXT part of the documentation mentions that escaping is done with '' but also claims it's not needed for CONTAINS ?!?

When performing queries with contractions and using the FREETEXT predicate, you must escape the quotation mark in the contraction; you do not have to do this when using the CONTAINS predicate.

For example, the following syntax fails:

…WHERE FREETEXT(*,'"We'll always have Paris"')

The correct syntax includes two single quotation marks.

The following syntax succeeds:

…WHERE FREETEXT(*,'"We''ll always have Paris"')

share|improve this answer
    
I guess the documentation and I will have to agree to disagree :-S. –  Kirk Liemohn Jun 5 '12 at 19:15

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.