0

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

1 Answer 1

7

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)];
8
  • 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, 2016 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, 2016 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, 2016 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, 2016 at 19:36
  • Can you actually mix bind expressions and literals like that?
    – Adrian Larson
    Aug 24, 2016 at 20:13

You must log in to answer this question.

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