+ Start a Discussion
Mark Mulholland 3Mark Mulholland 3 

SOQL search using a list. Want to use the equals operator with an Apex variable

Hello all,

I am trying to create a simple query against Accounts and Contacts, trying to see if a contact has a selected Email address and the parent Account has a specific IATA number. I am getting into some basic API creation and am using files from a Developerforce conference from 2 years ago. I modified the code to use field specific to my org

The SOQL query returns values only if I use the LIKE operator and doesn't return any results if I use the equals operator. See below for what I mean
 
String searchText = '%'+searchTerm1+'%';
                List<Contact> searchResults = [SELECT Id, Name, Phone, Email, Account.IATA_Code__c FROM Contact WHERE Account.IATA_Code__c LIKE :searchText];
                
                if(searchResults != null && searchResults.size() > 0) {
                    response.acctList = searchResults;
                    response.status = 'Success';
                    response.message = searchResults.size() + ' Contacts were found that matched your search term.';
                }
                else {
                    response.status = 'Error';
                    response.message = 'No Contacts where found based on that IATA Code, please search again.';
                }

Because I used LIKE in the SOQL query I get results returned. However if I modify the code as you can see in the next fragment I get no results
 
String searchText = '%'+searchTerm1+'%';
                List<Contact> searchResults = [SELECT Id, Name, Phone, Email, Account.IATA_Code__c FROM Contact WHERE Account.IATA_Code__c = :searchText];
                
                if(searchResults != null && searchResults.size() > 0) {
                    response.acctList = searchResults;
                    response.status = 'Success';
                    response.message = searchResults.size() + ' Contacts were found that matched your search term.';
                }
                else {
                    response.status = 'Error';
                    response.message = 'No Contacts where found based on that IATA Code, please search again.';
                }

However, if you do not use a List for the results and simply use a String, then you can use the equals operator.

Is this expected behaviour? and if so is there any way I can made the search more selective? or make it pseudo equals?

Thanks
Best Answer chosen by Mark Mulholland 3
Vivek DVivek D
You are using  adding "%" infront and back of your search text this is the issue "%" only works with LIKE other wise it will treat it as a string only for example :-
Account Name1 :- Boom
Account Name2 :- Ka Boom
  1. LIKE '%Boom%' will return both accounts
  2. = 'Boom' will return only first acount
  3. = '%Boom%' will not return anything because there is no account with name %Boom%
= will only return exact match
LIKE is used for matching a string in the text which supports wild cards like % means anything before/after, "_" escaping a text etc.  

All Answers

Vivek DVivek D
You are using  adding "%" infront and back of your search text this is the issue "%" only works with LIKE other wise it will treat it as a string only for example :-
Account Name1 :- Boom
Account Name2 :- Ka Boom
  1. LIKE '%Boom%' will return both accounts
  2. = 'Boom' will return only first acount
  3. = '%Boom%' will not return anything because there is no account with name %Boom%
= will only return exact match
LIKE is used for matching a string in the text which supports wild cards like % means anything before/after, "_" escaping a text etc.  
This was selected as the best answer
Mark Mulholland 3Mark Mulholland 3
Thanks very much Vivek. I had a feeling it would be something small but wasn't sure what

Mark
Vivek DVivek D
Hi Mark,
Please select best answer if that has solved your problem 
Thank You