SOQL querying ID's only returning first result

Question

I have a custom object called "Call_List__c" which has a field named "Call_List_Members__c". This field has a list of Contact Id's separated by commas.

In an apex class, I split the "Call_List_Members__c" field into a list and then I'm doing an SOQL query to pull the all of the contact records from the list.

Please see code below:

List<String> contactIds = new List<String>(); 
contactIds = callList[0].Call_List_Members__c.split(',');

List<Contact> callListMembers = [
  SELECT Id, Name, Title, Department, Phone, Email
  FROM Contact
  WHERE Id IN :contactIds
];

return callListMembers;

The problem here is that when I return the callListMembers query, there is only one result. Is there something that I've overlooked here? I want to return all of the records based on the ID's in the Call List Members field.

Answer 1

You need to strip the whitespace from your Ids before you can query for them. Apex checks this for you when you write Ids literally in a query:

System.debug([SELECT Id FROM Contact WHERE Id IN (' 0033600001aXXXXAAI')]);

SELECT Id FROM Contact WHERE Id IN (' 0033600001aXXXXAAI') ^ ERROR at Row:1:Column:30 invalid ID field: 0033600001aXXXXAAI

but it does not when you bind a collection:

Set<String> ids = new Set<String>{' 003000000000001'};
System.debug([SELECT Id FROM Contact WHERE Id IN :ids]);

20:13:06:009 USER_DEBUG [3]|DEBUG|()

Instead, it just returns no results. To avoid this, you'll have to iterate over the collection and use the trim() method to remove whitespace from the start and end of your Ids.

Alternately, you can skip around the issue by passing a regex to split():

for (String s :'0037F00000D3QJQ, 0037F00000D3QJU, 0037F00000D3QJH'.split('\\s*,\\s*')) 
    System.debug('\'' +s + '\'');

20:18:52:002 USER_DEBUG [1]|DEBUG|'0037F00000D3QJQ'

20:18:52:002 USER_DEBUG [1]|DEBUG|'0037F00000D3QJU'

20:18:52:002 USER_DEBUG [1]|DEBUG|'0037F00000D3QJH'

The regex eats the interstitial spaces as part of the delimiter, but note that it will not remove any leading or trailing space on the overall string.