Pfthb

I am trying to get all records whose name starts with "a"
and the second letter is in the range [a-s]

The quires I tried but no luck:

FROM 
 Agreement__c 
WHERE 
Account__r.FirstName LIKE 'a[a-s]%'

or

FROM 
 Agreement__c 
WHERE 
Account__r.FirstName LIKE 'a%'
AND Account__r.FirstName LIKE '_[a-s]%'

It is very strange because this works as expected

 FROM 
 Agreement__c 
WHERE 
Account__r.FirstName LIKE 'a%'
AND Account__r.FirstName LIKE '_s%' 

but every time I use range even like that no luck ...

Account__r.FirstName LIKE '[a-s]%'

regex expressions are not supported in SOQL queries.

It is very strange because this works as expected

FROM 
Agreement__c WHERE Account__r.FirstName LIKE 'a%' AND Account__r.FirstName LIKE '_s%'

it works as you expect, because LIKE 'a%' means field starts with a after could be zero or more any chanacters, LIKE '_s%' means query all records where FirstName starts from any exactly one character, second is s and after s zero or more any characters. Combination of two conditions gives your expected result.

in order to achieve desired result with SOQL, you have to manually include all symbols in condition:

WHERE 
Account__r.FirstName LIKE 'a%'
AND 
 (
 Account__r.FirstName LIKE '_a%'
 OR Account__r.FirstName LIKE '_b%'
 OR Account__r.FirstName LIKE '_c%'
..

You can actually do string comparison using greater than/less than operators.

SELECT Field__c FROM MyObject__c
WHERE Name >= 'aa'
AND Name < 'at'