6

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?

14

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;

    }
  • 3
    +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! – eyescream Nov 21 '12 at 23:33
  • Is this still the current best practice for this? – Xtremefaith Dec 31 '14 at 20:01
8

The easiest way is to use the array is as given below.

List<String> operators = new List<String> {'AND','OR'};
soqlString = 'SELECT Name FROM ACCOUNT WHERE Name != null';
soqlString += ' AND Operator__c in :operators';

Apex #automagically converts the reference to collection variables in string query to the corresponding sequence of strings inside the SOQL query.

Just another magic!

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.