I am trying to make a SOQL query trying to match email ids with lower case:

SELECT Id, Custom_Email_Id from Custom_Object where Custom_Email_Id IN :list_of_email_ids

Here, I am trying to get all objects with its lower cased Custom_Email_Id in list_of_email_ids. I have tried searching in documentation but couldn't find an SOQL function to lower case string.

If this is not possible, is there any other way this could be done?

  • I'm curious as to the use case of this; case-insensitive id searching could return "duplicates" unless you're using the full 18-character id.
    – Mike Chale
    Jan 28 '14 at 18:57
  • 3
    Note that case sensitivity only matters if the field itself is case sensitive. For example, SELECT Id, Name FROM Account WHERE Name IN ('test','foo','bar') returns accounts with names: TeSt, Foo, BaR, bAr...
    – sfdcfox
    Jan 28 '14 at 19:05
  • @sfdcfox Thanks for the info. I am matching on field type Email so I am guessing something like SELECT Id, Email FROM Account WHERE Email IN ('TesT@asd.com','Foo@asd.com') would return Id, Email for test@asd.com and foo@asd.com?
    – sPaz
    Jan 28 '14 at 19:29
  • 1
    @sPaz Yes, email fields are not case sensitive by default, and use normal case folding (meaning upper- and lower-case letters are equal).
    – sfdcfox
    Jan 28 '14 at 20:14

I don't believe you can do this through straight SOQL. You'd likely need to adjust case in Apex before running the SOQL query.

Something like

Set<String> list_of_email_ids_lc = new Set<String>();
for(String emailId : list_of_email_ids){
List<Custom_Object> objects = [SELECT Id, Custom_Email_Id from Custom_Object where Custom_Email_Id IN :list_of_email_ids_lc];

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.