clear markdown compare help donate or buy diddy dollings comments/suggestions/bugs a leap of faith? bible365
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 648513 fiddles created (15237 in the last week).

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
 hidden batch(es)


-- test case #1 -- test for fullname using firstname and lastname (well-formed). SELECT GetNamePart('Godfrey Roach', 'L'), GetNamePart('Godfrey Roach', 'F'), GetNamePart('Godfrey Roach', 'M'), GetNamePart('Godfrey Roach', 'LMF');
GetNamePart('Godfrey Roach', 'L') GetNamePart('Godfrey Roach', 'F') GetNamePart('Godfrey Roach', 'M') GetNamePart('Godfrey Roach', 'LMF')
Roach Godfrey Roach Godfrey
 hidden batch(es)


-- test case #2 -- test for fullname using firstname, middlename and lastname (well-formed). SELECT GetNamePart('Godfrey Michael Roach', 'L'), GetNamePart('Godfrey Michael Roach', 'F'), GetNamePart('Godfrey Michael Roach', 'M'), GetNamePart('Godfrey Michael Roach', 'LMF');
GetNamePart('Godfrey Michael Roach', 'L') GetNamePart('Godfrey Michael Roach', 'F') GetNamePart('Godfrey Michael Roach', 'M') GetNamePart('Godfrey Michael Roach', 'LMF')
Roach Godfrey Michael Roach Michael Godfrey
 hidden batch(es)


-- test case #3 -- test for fullname using firstname, middlename (short) and lastname (well-formed). SELECT GetNamePart('Godfrey M. Roach', 'L'), GetNamePart('Godfrey M. Roach', 'F'), GetNamePart('Godfrey M. Roach', 'M'), GetNamePart('Godfrey M. Roach', 'LMF');
GetNamePart('Godfrey M. Roach', 'L') GetNamePart('Godfrey M. Roach', 'F') GetNamePart('Godfrey M. Roach', 'M') GetNamePart('Godfrey M. Roach', 'LMF')
Roach Godfrey M. Roach M. Godfrey
 hidden batch(es)


-- test case #4 -- test for fullname using only firstname (but unknown, can also be the lastname or middlename). SELECT GetNamePart('Godfrey', 'L'), GetNamePart('Godfrey', 'F'), GetNamePart('Godfrey', 'M'), GetNamePart('Godfrey', 'LMF');
GetNamePart('Godfrey', 'L') GetNamePart('Godfrey', 'F') GetNamePart('Godfrey', 'M') GetNamePart('Godfrey', 'LMF')
 hidden batch(es)


-- test case #5 -- test for fullname using only firstname with some spaces (but unknown, can also be the lastname or middlename). SELECT GetNamePart(' Godfrey ', 'L'), GetNamePart(' Godfrey ', 'F'), GetNamePart(' Godfrey ', 'M'), GetNamePart(' Godfrey ', 'LMF');
GetNamePart(' Godfrey ', 'L') GetNamePart(' Godfrey ', 'F') GetNamePart(' Godfrey ', 'M') GetNamePart(' Godfrey ', 'LMF')
 hidden batch(es)


-- test case #6 -- test for fullname using firstname and lastname (using multiple spaces between name parts). SELECT GetNamePart(' Godfrey Roach ', 'L'), GetNamePart(' Godfrey Roach ', 'F'), GetNamePart(' Godfrey Roach ', 'M'), GetNamePart(' Godfrey Roach ', 'LMF');
GetNamePart(' Godfrey Roach ', 'L') GetNamePart(' Godfrey Roach ', 'F') GetNamePart(' Godfrey Roach ', 'M') GetNamePart(' Godfrey Roach ', 'LMF')
Roach Godfrey Roach Godfrey
 hidden batch(es)


-- test case #7 -- test for fullname using firstname, middlename (short) and lastname (using multiple spaces between name parts). SELECT GetNamePart('Godfrey M. Roach', 'L'), GetNamePart('Godfrey M. Roach', 'F'), GetNamePart('Godfrey M. Roach', 'M'), GetNamePart('Godfrey M. Roach', 'LMF');
GetNamePart('Godfrey M. Roach', 'L') GetNamePart('Godfrey M. Roach', 'F') GetNamePart('Godfrey M. Roach', 'M') GetNamePart('Godfrey M. Roach', 'LMF')
Roach Godfrey M. Roach M. Godfrey
 hidden batch(es)


-- test case #8 -- test for fullname using firstname, middlename and lastname, but using a invalid format value. SELECT GetNamePart('Godfrey Michael Roach', 'X'), GetNamePart('Godfrey Michael Roach', 'Y'), GetNamePart('Godfrey Michael Roach', 'Z'), GetNamePart('Godfrey Michael Roach', 'XYZ');
GetNamePart('Godfrey Michael Roach', 'X') GetNamePart('Godfrey Michael Roach', 'Y') GetNamePart('Godfrey Michael Roach', 'Z') GetNamePart('Godfrey Michael Roach', 'XYZ')
 hidden batch(es)


-- test case #9 -- test for fullname using firstname, middlename and lastname, but using a empty format value. SELECT GetNamePart('Godfrey Michael Roach', ''), GetNamePart('Godfrey Michael Roach', ''), GetNamePart('Godfrey Michael Roach', ''), GetNamePart('Godfrey Michael Roach', '');
GetNamePart('Godfrey Michael Roach', '') GetNamePart('Godfrey Michael Roach', '') GetNamePart('Godfrey Michael Roach', '') GetNamePart('Godfrey Michael Roach', '')
 hidden batch(es)