function readOnly(count){ }
Sign Up ›
Starting November 20, the site will be set to read-only. On December 4, 2023,
forum discussions will move to the Trailblazer Community.
Learn More

Browse by Topic
ShowAll Questionssorted byDate Posted
+ Start a Discussion
Andy Freeston_LarterAndy Freeston_Larter 

Case insensitive queries with Platform Encryption

I am trying to do a SOQL case insensitive text search on fields that have been encrypted with a “Deterministic – Case Insensitive” key. It seems that the query will only match case sensitive.

I have a Contact with the FirstName as “Andy”. Matching “Andy” results in a match but “andy” or “ANDY” results in no match. 
SELECT Id, Name FROM Contact WHERE FirstName='Andy' => Found
SELECT Id, Name FROM Contact WHERE FirstName='andy' => Not found
SELECT Id, Name FROM Contact WHERE FirstName='ANDY' => Not found

Is this Salesforce Platform Encryption working as intended?
Is it possible to make the SOQL query case insensitive?

Thanks.
- Andy
AnkaiahAnkaiah (Salesforce Developers) 
Hi Andy,



I have tested from end SOQL working for all scenarios as you mentioned in the description.

By default SOQL is case insensitive.

Please refer the below similar answer from stack exchange.

https://salesforce.stackexchange.com/questions/140390/make-soql-case-insensitive

If this helps, please mark it as best answer.

Thanks,
Ankaiah

 
Andy Freeston_LarterAndy Freeston_Larter
"By default SOQL is case insensitive" is true for non-encrypted fields. The problem is that once the field is encrypted, even with a deterministic case insensitive encryption key, the comparison becomes case sensitive.

I need a work around so I can do case insensitive queries.