use string array in IN clause in dynamic soql

  • I have an array of strings i'd like to use in the IN clause of a dynamic soql query.

    if i wasn't building the string at runtime i could simply use (from the SF docs):

    String[] ss = new String[]{'a', 'b'};
    Account[] aa = [SELECT Id FROM Account 
                    WHERE AccountNumber IN :ss];

    however, as i'm building at runtime, i use the following (where operators is my array):

    soqlString += ' and Operator__c in' + operators;

    This errors because the string values in operators are not in quotes.

    Is there an easy way to replicate the first example in dynamic SOQL or any other suggestion?

  • techtrekker

    techtrekker Correct answer

    9 years ago

    This post by @ca_peterson explains you can directly use bind variables in dynamic soql

    Alternatively, you can use this utility method to convert a Set into a String, which can be used in Dynamic SOQL.

     //convert a Set<String> into a quoted, comma separated String literal for inclusion in a dynamic SOQL Query
        private String quoteKeySet(Set<String> mapKeySet)
            String newSetStr = '' ;
            for(String str : mapKeySet)
                newSetStr += '\'' + str + '\',';
            newSetStr = newSetStr.lastIndexOf(',') > 0 ? '(' + newSetStr.substring(0,newSetStr.lastIndexOf(',')) + ')' : newSetStr ;
    System.debug('quoteKeySet() :  newSetStr ============ ' + newSetStr);   
            return newSetStr;

    +1 for bind variables in dynamic soql (I knew it but it's a nice trick nonetheless). But shame on you ;) with Winter'13 we finally got `String.join()`, no more loops like that!

    Is this still the current best practice for this?

License under CC-BY-SA with attribution

Content dated before 7/24/2021 11:53 AM