2

We have a custom object with fields that are alphanumeric plus * ' ( ) and - . My query works fine for everything but a single-quote. I need to search a specific field (in this example it is Name) IN a group of strings. Below is for matching on pgm.Name__c. If I do not select a program name that has a single-quote then the query returns lots of records perfectly. If I include the Alzheimer's program name ( or Children's or anything with 's then I get an error message. expecting a right parentheses, found 's'

SELECT .... FROM Program__c pgm WHERE ( pgm.Name__c IN ( 'Alzheimer's Disease Initiative (ADI)', 'Adult Care Food Program (ACFP)', 'AmeriCorps*VISTA (Volunteers in Service to America)' ) ) 

If I use escapeSingleQuotes then I get the following: enter image description here

Of course pgm.Name will never match any of these. Is there a way to reformat pgm.Name__c to match the reformatted items in the where clause? If I replace 's with s or anything else then it will not match. I need pgm.Name__c to look in he strings and pull back all the records it matches. Any ideas?

2 Answers 2

2

If you pass literal string to IN operator, you need to escape the single quote within the string only.

'Alzheimer\'s Disease Initiative (ADI)'
3
  • Do replaceAll('\'s,', '\\'s'); ? escapeSingleQuotes did all single-quotes.
    – user46685
    Jun 24, 2020 at 23:08
  • You cannot use apex method inside the soql. However you can use it in building a list as in the example given by @MarkPond before passing it to the soql Jun 25, 2020 at 0:08
  • I learned that I cannot use apex method inside the soql. I was able to get WHERE ( pgm.Name__c IN ( 'Alzheimer\'s Disease Initiative (ADI)', 'AmeriCorps State Grants' ) ) by using escapeSingleQuotes in the code where I was constructing the string. dataQueryStr += '\'' + String.escapeSingleQuotes(so.getLabel()) + '\''; So when it put the whole query string together it had what you showed me with the escape single quote only within the string. Thanks, Hengky.
    – user46685
    Jun 25, 2020 at 12:47
1

Solving the issue as well as jumping ahead a little bit in your implementation, using a List<String> and variable binding in addition to escaping single quotes is probably going to be your best solution long term.

This implementation is also a lot more readable and less error prone.

List<String> programNames = new List<String>();
programNames.add('Alzheimer\'s Disease Initiative (ADI)');
programNames.add('Adult Care Food Program (ACFP)');
programNames.add('AmeriCorps*VISTA (Volunteers in Service to America)');

pgm.Contact__r.MailingPostalCode, pgm.Keywords__c FROM Program__c
WHERE Pgm.Name__c IN :programNames
ORDER BY pgm.Name__c
2
  • Actually I do have a List. In my post I showed what it turned into. programssearchQuery += ' WHERE ( pgm.Name__c IN ( ' + dataQueryStr + ' ) ) ORDER BY pgm.Name__c' ; My dataQueryStr is all the things you have in your programNames.add and are separated by commas.
    – user46685
    Jun 25, 2020 at 11:28
  • don't stringify the IN collection, bind to the list directly
    – Mark Pond
    Jun 25, 2020 at 16:31

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .