0

Let's say there are 5 accounts in the system as below:

  1. Account1
  2. ACCOUNT1
  3. account1
  4. ACCount1
  5. acCOUNT1

I want the SOQL query to return me only 1 record: account1

So i tried the below query:

Select Id, Name from Account where name = 'account1'

But this returns me all the 5 records. How can I overcome this?

1
  • You can check my solution which should work for you. Aug 8, 2018 at 13:45

4 Answers 4

4

SOQL is almost always case-insensitive:

String comparisons using the equals operator are case-sensitive for unique case-sensitive fields and case-insensitive for all other fields.

Account Name is not a field that meets these conditions (unique case-sensitive), which are part of the definition of a field. That combination of attributes is often seen on a custom External Id field, although it can occur elsewhere as well.

Most parts of Salesforce are, in fact, case-insensitive. To compare strings for case-sensitive equality, you must use the String.equals() method. Note that the Apex == operator is also case-insensitive.

You would need to perform the query and then iterate over the results in Apex to make case-sensitive comparisons in order to locate the exact, case-sensitive match.

3
  • You just beat me to it :)
    – Jayant Das
    Aug 8, 2018 at 13:31
  • Could you elaborate a bit on "Account Name is not a unique case-sensitive field, which would be an External ID."? You can actually mark non-unique fields to be external id's and a field which is unique is not necessarily marked as an external id. Aug 8, 2018 at 13:49
  • 1
    @rael_kid You're right, I oversimplified. Let me revise that.
    – David Reed
    Aug 8, 2018 at 13:50
2

I think you can not achieve it using the simple SOQL query only as SOQL is case-insensitive. You will have to use the Map collection for finding the records. In the Map collection you can have the name as the key as Map keys are case-sensitive.

Map<String, Account> mapAccounts = Map<String, Account>();
for( Account acc : [SELECT Id, Name FROM Account WHERE Name = 'Account1' ]){
   mapAccounts.put( acc.Name, acc );
}

mapAccounts.get('Account1') //will give you the account if it's there
5
  • This does not achieve the poster's objective of a case-sensitive match. It returns one account per case-insensitive Account name, and which is selected is essentially undefined.
    – David Reed
    Aug 8, 2018 at 13:37
  • @DavidReed I think I got it wrong. You are right. I will update my answer. Aug 8, 2018 at 13:39
  • I think you have a useful answer here. May I suggest explicitly pointing out why this works (String Map keys are case-sensitive) to make it more useful? I'd be happy to upvote.
    – David Reed
    Aug 8, 2018 at 13:42
  • @DavidReed - Now, I hope it makes some sense. Aug 8, 2018 at 13:44
  • Yes, much improved and an important note about how collections work! +1.
    – David Reed
    Aug 8, 2018 at 13:45
0

From the Force.com SOQL and SOSL Reference on WHERE conditionExpression:

comparisonOperator Case-insensitive operators that compare values.

Operator =
Name Equals
Description Expression is true if the value in the specified fieldName equals the specified value in the expression. String comparisons using the equals operator are case-sensitive for unique case-sensitive fields and case-insensitive for all other fields.
0

Currently, string comparison is case-insensitive

Check the documentation for further details SFDC ConditionExpression

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .