By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--
-- function to get a part of a fullname or to reformat the fullname.
-- @fullname - the fullname to get the part from or to reformat.
-- @format - the format of the output using F (firstname), M (middlename) and L (lastname).
-- the function returns the fullname in specified format or NULL if input is not valid
-- or the part of name is empty.
--
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).
-- test for fullname using firstname and lastname (well-formed).
DECLARE @fullname VARCHAR(100) = 'Godfrey Roach'
SELECT dbo.GetNamePart(@fullname, 'L'), dbo.GetNamePart(@fullname, 'F'), dbo.GetNamePart(@fullname, 'M')
SELECT dbo.GetNamePart(@fullname, 'LMF')
| (No column name) | (No column name) | (No column name) |
|---|---|---|
| Roach | Godfrey | null |
| (No column name) |
|---|
| Roach Godfrey |
-- test for fullname using firstname, middlename and lastname (well-formed).
DECLARE @fullname VARCHAR(100) = 'Godfrey Michael Roach'
SELECT dbo.GetNamePart(@fullname, 'L'), dbo.GetNamePart(@fullname, 'F'), dbo.GetNamePart(@fullname, 'M')
SELECT dbo.GetNamePart(@fullname, 'LMF')
| (No column name) | (No column name) | (No column name) |
|---|---|---|
| Roach | Godfrey | Michael |
| (No column name) |
|---|
| Roach Michael Godfrey |
-- test for fullname using firstname, middlename (short) and lastname (well-formed).
DECLARE @fullname VARCHAR(100) = 'Godfrey M. Roach'
SELECT dbo.GetNamePart(@fullname, 'L'), dbo.GetNamePart(@fullname, 'F'), dbo.GetNamePart(@fullname, 'M')
SELECT dbo.GetNamePart(@fullname, 'LMF')
| (No column name) | (No column name) | (No column name) |
|---|---|---|
| Roach | Godfrey | M. |
| (No column name) |
|---|
| Roach M. Godfrey |
-- test for fullname using only firstname (but unknown, can also be the lastname or middlename).
DECLARE @fullname VARCHAR(100) = 'Godfrey'
SELECT dbo.GetNamePart(@fullname, 'L'), dbo.GetNamePart(@fullname, 'F'), dbo.GetNamePart(@fullname, 'M')
SELECT dbo.GetNamePart(@fullname, 'LMF')
| (No column name) | (No column name) | (No column name) |
|---|---|---|
| null | null | null |
| (No column name) |
|---|
| null |
-- test for fullname using only firstname with some spaces (but unknown, can also be the lastname or middlename).
DECLARE @fullname VARCHAR(100) = ' Godfrey '
SELECT dbo.GetNamePart(@fullname, 'L'), dbo.GetNamePart(@fullname, 'F'), dbo.GetNamePart(@fullname, 'M')
SELECT dbo.GetNamePart(@fullname, 'LMF')
| (No column name) | (No column name) | (No column name) |
|---|---|---|
| null | null | null |
| (No column name) |
|---|
| null |
-- test for fullname using firstname and lastname (using multiple spaces between name parts).
DECLARE @fullname VARCHAR(100) = ' Godfrey Roach '
SELECT dbo.GetNamePart(@fullname, 'L'), dbo.GetNamePart(@fullname, 'F'), dbo.GetNamePart(@fullname, 'M')
SELECT dbo.GetNamePart(@fullname, 'LMF')
| (No column name) | (No column name) | (No column name) |
|---|---|---|
| Roach | Godfrey | null |
| (No column name) |
|---|
| Roach Godfrey |
-- test for fullname using firstname, middlename (short) and lastname (using multiple spaces between name parts).
DECLARE @fullname VARCHAR(100) = 'Godfrey M. Roach'
SELECT dbo.GetNamePart(@fullname, 'L'), dbo.GetNamePart(@fullname, 'F'), dbo.GetNamePart(@fullname, 'M')
SELECT dbo.GetNamePart(@fullname, 'LMF')
| (No column name) | (No column name) | (No column name) |
|---|---|---|
| Roach | Godfrey | M. |
| (No column name) |
|---|
| Roach M. Godfrey |
-- test for fullname using firstname, middlename and lastname, but using a invalid format value.
DECLARE @fullname VARCHAR(100) = 'Godfrey Michael Roach'
SELECT dbo.GetNamePart(@fullname, 'X'), dbo.GetNamePart(@fullname, 'Y'), dbo.GetNamePart(@fullname, 'Z')
SELECT dbo.GetNamePart(@fullname, 'XYZ')
| (No column name) | (No column name) | (No column name) |
|---|---|---|
| null | null | null |
| (No column name) |
|---|
| null |
-- test for fullname using firstname, middlename and lastname, but using a empty format value.
DECLARE @fullname VARCHAR(100) = 'Godfrey Michael Roach'
SELECT dbo.GetNamePart(@fullname, ''), dbo.GetNamePart(@fullname, ''), dbo.GetNamePart(@fullname, '')
SELECT dbo.GetNamePart(@fullname, '')
| (No column name) | (No column name) | (No column name) |
|---|---|---|
| null | null | null |
| (No column name) |
|---|
| null |