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.