I'm working on a Symfony 3 CRM, and I need to be able to create a list of customers which can be filtered by the first letter of the surname. The issue is, I need to split the column data first in order to find the surname - this is because the database is synced with Sage which requires data in a specific format so I cannot change it.
I discovered the
SUBSTRING_INDEX
$query = $em->createQuery("SELECT u FROM AppBundle:Customer c WHERE SUBSTRING_INDEX(SUBSTRING_INDEX(c.contact_name, ' ', 3), ' ', -1) LIKE :letter");
$query->setParameter('letter','%'.$letter.'%');
$letter
[Syntax Error] line 0, col 41: Error: Expected known function, got
'SUBSTRING_INDEX'
Doctrine doesn't recognize SUBSTRING_INDEX as a function.
You can import a Doctrine Extension to recognize it or change your query for a thing lke this sql LOWER(SUBSTRING(c.contact_name, LOCATE(' ', c.contact_name)-1))