1

What is the cleanest way to represent the following SQL in a SOQL query?

Where SubStr(MR_CONTRACTS.SPD_REF, 1, 2) In ('AM', 'TR', 'AH', 'AC', 'CT')

My SalesForce object name is also MR_Contracts The SalesForce field name is Contract_spd_ref__c

Tried the following with no success.

SELECT Name 
FROM MR_Contracts__c     
WHERE Contract_spd_ref__c LIKE 'AM%' 
AND Contract_spd_ref__c LIKE 'TR%' 
AND Contract_spd_ref__c LIKE 'AH%' 
AND Contract_spd_ref__c LIKE 'AC%' 
AND Contract_spd_ref__c LIKE 'CT%'

This returns the expected results. Still a longer query that I would like to shorten:

SELECT Name 
FROM MR_Contracts__c 
WHERE (Contract_spd_ref__c LIKE 'AM%' 
OR Contract_spd_ref__c LIKE 'TR%'
OR Contract_spd_ref__c LIKE 'AH%' 
OR Contract_spd_ref__c LIKE 'AC%' 
OR Contract_spd_ref__c LIKE 'CT%')
  • what do you mean no success? what was the error (if any)? or it simply doesnt retrieve the records you expected. – glls Aug 3 '17 at 21:39
  • By no success, I mean with shortened syntax. Was trying a shorter string like I do in SQL. – rickmac Aug 3 '17 at 21:42
  • When I use just one param I get expected results . If I use all as in the SELEC above I get no results. this is OK - SELECT Name FROM MR_Contracts__c WHERE Contract_spd_ref__c LIKE 'TR%' – rickmac Aug 3 '17 at 21:55
  • Updated the question using OR conditions. is there a shorter syntax suggestion? – rickmac Aug 3 '17 at 21:59
3

If you are using Apex there is a shorter way:

List<String> substrings = new List<String> { 'AM%', 'TR%', 'AH%', 'AC%', 'CT%' };
List<MR_Contracts__c> records = [
    SELECT Name FROM MR_contracts__c WHERE Contract_spd_ref__c LIKE :substrings
];

If you were using the IN clause, you could inline the collection. Unfortunately, this syntax is not supported in raw SOQL when using LIKE.

SELECT Name FROM MyObject__c WHERE MyField__c IN ('A', 'B', 'C') // will compile

SELECT Name FROM MyObject__c WHERE MyField__c LIKE ('A', 'B', 'C') // will not compile
|improve this answer|||||
  • Is substring supported with MyField__c. I'd like to just compare the first two characters to the IN Parms using MyObject__c WHERE MyField__c IN ('A', 'B', 'C') – rickmac Aug 4 '17 at 0:49
  • That's what the LIKE clause is for, but you have to use OR if you're using pure SOQL. – Adrian Larson Aug 4 '17 at 1:54

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.