Contacts with MiddleName values and queries

Question

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)

Answer 1

It transpires that this is a known issue with no resolution. The known issue explains that the "workaround" (which isn't actually an appropriate one for our customer with many contacts with the same first and last name) is essentially to use the third option, putting a wildcard between the first and last names and NOT entering the middle name text at all.

We are left with having to instruct users to never try typing the middle name in order to resolve the contact they are searching for, but rather having to scroll through the various results (based on first and last name only) to find the exact one, visually, by middle name. Very poor UX.

Update:

In the end we actually switched to using SOSL to handle Contact searches, but this was driven by the need to support Shield encryption on various Contact fields in order to allow our customers to meet their legal obligations. This effectively side-steps the problem with middle names (SOSL doesn't suffer the same way that SOQL does) though now means that our auto-completion results include matches against Contact's Account names not just personal names.