clear markdown compare help donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
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. 462189 distinct fiddles created so far.

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


-- 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');
(No column name) (No column name) (No column name) (No column name)
Roach Godfrey Roach Godfrey
 hidden batch(es)


-- 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');
(No column name) (No column name) (No column name) (No column name)
Roach Godfrey Michael Roach Michael Godfrey
 hidden batch(es)


-- 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');
(No column name) (No column name) (No column name) (No column name)
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 dbo.GetNamePart('Godfrey', 'L'), dbo.GetNamePart('Godfrey', 'F'), dbo.GetNamePart('Godfrey', 'M'), dbo.GetNamePart('Godfrey', 'LMF');
(No column name) (No column name) (No column name) (No column name)
 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 dbo.GetNamePart(' Godfrey ', 'L'), dbo.GetNamePart(' Godfrey ', 'F'), dbo.GetNamePart(' Godfrey ', 'M'), dbo.GetNamePart(' Godfrey ', 'LMF');
(No column name) (No column name) (No column name) (No column name)
 hidden batch(es)


-- 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');
(No column name) (No column name) (No column name) (No column name)
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 dbo.GetNamePart('Godfrey M. Roach', 'L'), dbo.GetNamePart('Godfrey M. Roach', 'F'), dbo.GetNamePart('Godfrey M. Roach', 'M'), dbo.GetNamePart('Godfrey M. Roach', 'LMF');
(No column name) (No column name) (No column name) (No column name)
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 dbo.GetNamePart('Godfrey Michael Roach', 'X'), dbo.GetNamePart('Godfrey Michael Roach', 'Y'), dbo.GetNamePart('Godfrey Michael Roach', 'Z'), dbo.GetNamePart('Godfrey Michael Roach', 'XYZ');
(No column name) (No column name) (No column name) (No column name)
 hidden batch(es)


-- 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', '');
(No column name) (No column name) (No column name) (No column name)
 hidden batch(es)