mysqlstored-procedureswhitespacetrimremoving-whitespace

TRIM in SELECT statement that works in Stand Alone Query Fails in Stored Procedure (MySQL)


New to stored procedure development. I'm developing a stored procedure where I create and populate a table with the results of a query, where I get an identifier number (DCNumber), FirstName, LastName, MiddleName, NameSuffix (and other data) from a Department of Corrections database.

The chunk of stored procedure at issue is this:

CREATE TABLE `full_record_valid_probation` 
        (id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        DCNumber VARCHAR(6), 
        LastName VARCHAR(50),
        FirstName VARCHAR(50),
        MiddleName VARCHAR(50), 
        NameSuffix VARCHAR(10), 
        Race VARCHAR(2), 
        Sex VARCHAR(2), 
        BirthDate VARCHAR(10), 
        SupervisionTerminationDate VARCHAR(10), 
        ReceiptDate VARCHAR(10), 
        supvtype_description VARCHAR(255), 
        facility_description VARCHAR(100), 
        supvstatus_description VARCHAR(255), 
        race_descr VARCHAR(50))
    SELECT t1.DCNumber, `t1`.`LastName`, `t1`.`FirstName`, `t1`.`MiddleName`, `t1`.`NameSuffix`, Race, Sex, BirthDate, SupervisionTerminationDate, ReceiptDate, supvtype_description, facility_description, supvstatus_description, race_descr 
    FROM `fl_doc`.`offender_root` t1
    INNER JOIN `fl_doc`.`dcnumbers_by_county_convicted` t2 ON t1.DCNumber = t2.DCNumber
    WHERE t1.SupervisionTerminationDate <> ''
    AND t1.FirstName <> '' AND t1.LastName <> ''
    AND t1.supvtype_description LIKE 'PROBATION%';

This works as is, except that I noticed some last names (LastName) don't sort correctly because they have a leading space in the name. The space was introduced at the source, but now I need to clean the values of leading and trailing spaces for my own use.

Ex: enter image description here

The above table snippet is from the table after the stored procedure runs. Everything is fine, but you can see the last name "JOHNSON" comes first in a sort by LastName. That's because there is a leading space in " JOHNSON".

Then I tried to use TRIM in the SELECT statement, like so:

SELECT t1.DCNumber, trim(`t1`.`LastName`), trim(`t1`.`FirstName`), `t1`.`MiddleName`, `t1`.`NameSuffix`, Race, Sex, BirthDate, SupervisionTerminationDate, ReceiptDate, supvtype_description, facility_description, supvstatus_description, race_descr 
    FROM `fl_doc`.`offender_root` t1
    INNER JOIN `fl_doc`.`dcnumbers_by_county_convicted` t2 ON t1.DCNumber = t2.DCNumber
    WHERE t1.SupervisionTerminationDate <> ''
    AND t1.FirstName <> '' AND t1.LastName <> ''
    AND t1.supvtype_description LIKE 'PROBATION%';

This works in a stand alone query, but it doesn't work inside a stored procedure. When used in a stored procedure, it does this:

enter image description here

As you can see in the field name header, instead of performing the TRIM operation on trim(LastName) and trim(FirstName), it reads these literally and makes field names called 'trim(FirstName)' and 'trim(LastName)'.

Is there a different approach to space removal when constructing a stored procedure?

Thanks ahead.


Solution

  • If an output column is an expression, and alias name is not assigned explicitly, then the whole expression text is used as column name. So simply assign alias name to the expression:

    SELECT ..., trim(`t1`.`LastName`) AS LastName, ...