SOQL SubStr In equivalent

Question

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%')

Answer 1

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