As per this previous question, I believe that Salesforce (still!) has a bug in querying Contact instances when middle names are enabled and set.
If I have a Contact with FirstName "Mohammed", MiddleName "Hussain" and LastName "Ahmed" for example, I observe the following behaviour using an SOQL query of the form "SELECT Id, Name FROM Contact WHERE Name LIKE "value":
- value = "%mohammed ahmed%", no row is selected
- value = "%mohammed hussain ahmed%", no row is selected
- value = "%mohammed%ahmed%", the contact's row is selected
- value = "%mohammed%hussain%ahmed%", no row is selected
Basically, I observe that the existence of a middle name prevents direct "LIKE" matching of the name and inclusion of the middle name in the search term value prevents selection at all.
I looked at using SOSL instead but still found that it didn't behave as I would expect.
Does anyone have a solution to this? (Note that our customer has many Contact instances with the same first and last names and therefore want to distinguish using the middle name. Additionally, they have Contact instances with middle and last names that include spaces and I have seen this, too, interfere with name matching unless I replace the spaces in the search value with percents)