I want to query for accounts based on what the user types in a text field but rather than being totally dependent on the user to come through and type it correctly (lower or upper case) what I want to do is query the right opportunities with names that are equal in upper case.

This is what I have but you can't do this

List<Account> temp = [Select Name,ID,LastModifiedByID from Account WHERE Name.toUpperCase =: searchField ORDER BY LastModifiedDate Asc];

For ex. User enters "Hello" (name of opportunity is HeLLo) What I want to do is query it in a way that I compare HELLO and HELLO so I return the right results.

I'm not sure if this is possible. Another way I thought was querying all opportunities and comparing within a loop but that will be outrageous when I have a large number of records to process


Queries filter in a case-insensitive manner. There's no need to convert either operand to uppercase to get the correct results; however, keep in mind that you need to use "LIKE" if you want to find part of the full field's value.

Opportunity[] opps = [SELECT Name FROM Opportunity WHERE Name LIKE :'%'+searchTerm+'%'];

If you're having problems with a query, consider using a search instead:

Opportunity[] opps = [FIND :searchTerm IN NAME FIELDS RETURNING Opportunity(Name)][0];

From the SOSL, you can then pull in all matching accounts:

Account[] accounts = [SELECT Name, LastModifiedById FROM Account
                      WHERE Id IN (SELECT AccountId FROM Opportunity
                          WHERE Id IN :opps)];
  • What do you mean by keep in mind that you need to use "LIKE" if you want to find part of the full field's value.? Aug 24 '16 at 19:31
  • @user2582622 For example, given "searchTerm" of "Hello", Name = :searchTerm will not match "Hello World", but will match "HeLlO". You need to use LIKE if you want to find just part of the field's value; "Hello%" will match "HELLO World", for example.
    – sfdcfox
    Aug 24 '16 at 19:32
  • Okay, now I see what you're saying. I'm assuming if its 'Hello WOrLd' then it will match, correct? Aug 24 '16 at 19:35
  • @user2582622 You got it. Use "=" for case-insensitive match of the full field contents, and "LIKE" for a partial match.
    – sfdcfox
    Aug 24 '16 at 19:36
  • Can you actually mix bind expressions and literals like that?
    – Adrian Larson
    Aug 24 '16 at 20:13

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.