1

I am trying to create a free text search in SharePoint 2010 using code behind. The code to create my query is shown below:

StringBuilder query = new StringBuilder();
query.Append("SELECT FileLeafRef, Title, Facility ");
query.Append("FROM Scope() ");
query.Append("WHERE (\"SCOPE\" = 'Order') ");
query.Append("AND ImportStatus <> 'Deleted' ");
query.Append(string.Format("AND FREETEXT ('{0}*') ", searchWord));
return query.ToString();

While this works for singe word searches, it does not work for searches that contain apostrophes (i.e. the word physician's). If I do search for the word "physician's" I get a malformed query exception.

I have tried html encoding the query (didn't work), tried back-slashing the apostrophe (didn't work) and tried using double apostrophes as an escape sequence (that didn't work either). I have Googled high and low for a solution but can't find one. Some solutions proposed on this site with similar questions hasn't helped either.

How can I make my query work with apostrophes and other special characters? There must be away.

0

Did you try to add double slash by modifying the code as below:-

StringBuilder query = new StringBuilder();
query.Append("SELECT FileLeafRef, Title, Facility ");
query.Append("FROM Scope() ");
query.Append("WHERE (\"SCOPE\" = 'Order') ");
query.Append("AND ImportStatus <> 'Deleted' ");
query.Append(string.Format("AND FREETEXT ('{0}*') ", searchWord.Replace("\'","\\'")));
return query.ToString();

For other special characters I guess SharePoint would handle it, this apostrophe is actually breaking the SQL Search query for SharePoint Search hence the malformed query exception is there.

0

Not sure why it didn't work the first time, but using an apostrophe to escape the apostrophe is the correct answer. So in my example physician's would become physician''s

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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