We have a number of users who will write their case notes in Word, then paste them into Salesforce. We have now implemented a custom search on case notes (and a few other fields) and have found a problem.

• Text pasted from word = "Mary O’Connor"
• Text typed into salesforce = "Mary O'Connor"

Querying "select CaseNotes__c from Case where CaseNotes__c like 'Mary O'Connor'" does not return the record with Mary O’Connor (with the curly quotes).

Any suggestions other than a) getting them to turn off curly quotes in word or b) getting them to always search on only the part of the word that does not contain the apostrophe?

(I think that EM dashes, umlats accents and any other special characters will be the same. Querying where CaseNotes__c like 'Amelie' would probably not return Amélie either).

Attribution to: JodieM

Possible Suggestion/Solution #1

I think it is a good idea to use a escapeSingleQuotes(...) method of the String class:

Returns a String with the escape character () added before any single quotation marks in the String

// The search query
String query;

// Now creating a dynamic SOQL
query += 'select CaseNotes__c from Case ';
query += ' where CaseNotes__c like \''+  String.escapeSingleQuotes(searchString) + '\'';

So in your case the query will looks like:

select CaseNotes__c from Case  where CaseNotes__c like 'Mary O\'Connor'

Attribution to: Sergej Utko
This content is remixed from stackoverflow or stackexchange. Please visit https://salesforce.stackexchange.com/questions/31855