|
|
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
|
