Krdytkyu

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'