I have been trying to use TRIM for the first time. I am havnig trouble with the BOTH option. Here is how I have been assembling the command...
$commandPart1 = "SELECT Serial, Name, " .
"TRIM(LEADING '.' FROM CONCAT(NameModelPrefix, '.', NameModel, NameModelSuffix)) AS NameMdl, " .
"LENGTH(TRIM(LEADING '.' FROM CONCAT(ManufacturerModelPrefix, ManufacturerModel, ManufacturerModelSuffix))) AS Len " .
"FROM AirframeVariant " .
"WHERE FamilySN=" . $refFamilySN;
$commandPart2 = "SELECT Serial " .
"TRIM(BOTH ' ' FROM CONCAT(Name, ' ', NameMdl)) AS Mdl, " .
"LENGTH(TRIM(BOTH ' ' FROM CONCAT(Name, ' ', NameMdl))) AS Len " .
"FROM (" . $commandPart1 . ") a1";
$commandPart3 = "SELECT Serial " .
"FROM (" . $commandPart2 . ") a2 " .
"WHERE Mdl LIKE \"%" . $refModel . "%\" " .
"ORDER BY Len";
...which assembles as...
SELECT Serial FROM (SELECT Serial TRIM(BOTH ' ' FROM CONCAT(Name, ' ', NameMdl)) AS Mdl, LENGTH(TRIM(BOTH ' ' FROM CONCAT(Name, ' ', NameMdl))) AS Len FROM (SELECT Serial, Name, TRIM(LEADING '.' FROM CONCAT(NameModelPrefix, '.', NameModel, NameModelSuffix)) AS NameMdl, LENGTH(TRIM(LEADING '.' FROM CONCAT(ManufacturerModelPrefix, ManufacturerModel, ManufacturerModelSuffix))) AS Len FROM AirframeVariant WHERE FamilySN=3) a1) a2 WHERE Mdl LIKE "%Mustang IV%" ORDER BY Len
...but unfortunately produces this error...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIM(BOTH ' ' FROM CONCAT(Name, ' ', NameMdl)) AS Mdl, LENGTH(TRIM(BOTH ' ' FROM' at line 1
I have been reading the online manuals, but I don't see what I am doing wrong. Apparently my service provided uses MySQL 5.0.
Thanks in advance for any assistance you can provide.
Mike
