CREATE FUNCTION GetNamePart(@fullname VARCHAR(200), @format VARCHAR(30)) RETURNS VARCHAR(200) AS BEGIN -- replace multiple spaces of the fullname and trim the result. SET @fullname = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(@fullname, ' ', '<>'), '><', ''), '<>', ' '))) -- get the different name parts (firstname, middlename and lastname) of the fullname. DECLARE @first_name VARCHAR(100) SET @first_name = LTRIM(RTRIM(LEFT(@fullname, CHARINDEX(' ', @fullname)))) DECLARE @last_name VARCHAR(100) SET @last_name = LTRIM(RTRIM(RIGHT(@fullname, CHARINDEX(' ', REVERSE(@fullname))))) DECLARE @middle_name VARCHAR(100) SET @middle_name = LTRIM(RTRIM(SUBSTRING(@fullname, LEN(@first_name) + 1, LEN(@fullname) - LEN(@first_name) - LEN(@last_name)))) -- init the formatted name of the fullname. DECLARE @formatted_name VARCHAR(100) -- return only the formatted name if format string is valid. IF PATINDEX('%[^LMF]%', UPPER(@format)) > 0 SET @formatted_name = '' ELSE BEGIN SET @format = REPLACE(REPLACE(REPLACE(@format, 'M', '##M##'), 'L', '##L##'), 'F', '##F##') SET @formatted_name = LTRIM(RTRIM(REPLACE(REPLACE(REPLACE(UPPER(@format), '##F##', @first_name + ' '), '##M##', @middle_name + ' '), '##L##', @last_name + ' '))) END -- check the input (@fullname) for valid value (firstname, lastname or firstname, middlename, lastname). IF PATINDEX('%_ %_% _%', @fullname) = 0 AND PATINDEX('%_ _%', @fullname) = 0 SET @formatted_name = '' -- return the new formatted name and replace multiple spaces. RETURN NULLIF(REPLACE(REPLACE(REPLACE(@formatted_name, ' ', '<>'), '><', ''), '<>', ' '), '') END

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

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

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

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

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

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

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

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

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