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]%'
up vote 4 down vote accepted

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%'
..
  • Yeas that is true and that would work (even if it is the most beautiful code to see) but it is hard to believe that char ranges can not be used here ! – Boris Gichev Nov 15 at 7:58
  • @BorisGichev yes, it is hard to believe, but this is true. you can read more about LIKE operator – Oleksandr Berehovskiy Nov 15 at 7:59
  • Yes I read this, but thought that I am missing something. Ok, thank you Olek. – Boris Gichev Nov 15 at 8:02
  • 1
    Ahaa you use the reg ex via the fields, because they can use REGEX :). Nice to have that trick in mind. I will go for the first option since I am queering the objects for a document. And it would be faster now. But if I need that again I might consider creating a few filter fields in the objects that I need to query. Thank you for the efforts ! – Boris Gichev Nov 15 at 8:14
  • 2
    REGEX is only available in Validation Rules and Process Builder? Or did they change that? – Adrian Larson Nov 15 at 14:04

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

SELECT Field__c FROM MyObject__c
WHERE Name >= 'aa'
AND Name < 'at'
  • how > and < works, summation of Ascii values? – Pranay Jaiswal Nov 15 at 14:15
  • 2
    It should have the same mechanics as ORDER BY. – Adrian Larson Nov 15 at 14:45
  • This looks good, but first I have to check the char table because the organization uses characters like öäå and that may cause unwanted behavior. Or maybe just make one dedicated filter only for those characters. – Boris Gichev Nov 16 at 15:12

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.