|
|
CREATE FUNCTION GetNamePart(fullname VARCHAR(200), format VARCHAR(30))
RETURNS VARCHAR(200) DETERMINISTIC NO SQL
BEGIN
-- set the fullname and format to an internal variable.
DECLARE strFullname VARCHAR(200) DEFAULT TRIM(REPLACE(REPLACE(REPLACE(fullname, ' ', '<>'), '><', ''), '<>', ' '));
DECLARE strFormat VARCHAR(30) DEFAULT UPPER(TRIM(format));
-- set the firstname, middlename and lastname to empty string.
DECLARE strFirstname, strMiddlename, strLastname VARCHAR(200) DEFAULT '';
-- get the firstname, middlename and lastname from fullname.
SET strFirstname = LEFT(strFullname, LOCATE(' ', strFullname));
SET strLastname = RIGHT(strFullname, LOCATE(' ', REVERSE(strFullname)));
SET strMiddlename = SUBSTRING(strFullname, LENGTH(strFirstname) + 1, LENGTH(strFullname) - LENGTH(strFirstname) - LENGTH(strLastname));
-- replace the format chars (L, M, F) as placeholder for later replacement with values.
SET strFormat = REPLACE(REPLACE(REPLACE(UPPER(format), 'L', '##L##'), 'M', '##M##'), 'F', '##F##');
-- set the values (firstname, middlename, lastname) to the placeholder.
SET strFormat = TRIM(REPLACE(REPLACE(REPLACE(strFormat, '##L##', CONCAT(strLastname, ' ')), '##M##', CONCAT(strMiddlename, ' ')), '##F##', CONCAT(strFirstname, ' ')));
-- clear the formatted value if the format string is not valid or the fullname is not a valid format.
SET strFormat = IF(REGEXP_INSTR(UPPER(format), '[^FML]') > 0, '', strFormat);
SET strFormat = IF(REGEXP_LIKE(strFullname, '.+ .+ .+') = 0 AND REGEXP_LIKE(strFullname, '.+ .+') = 0, '', strFormat);
-- trim and replace multiple spaces on the formatted value.
SET strFormat = TRIM(REPLACE(REPLACE(REPLACE(strFormat, ' ', '<>'), '><', ''), '<>', ' '));
-- return the formatted value.
RETURN NULLIF(strFormat, '');
END
|
