SQL Function to Parse First Name from Full Name
Categories: Blog |
Author:
David O'Leary |
Posted:
9/29/2010 |
Views:
8688
Ever needed to get a users first name out of a SQL table or field where only the full name was collected? We've create a SQL Function that does a pretty good job of this.
This is partially based on a script provided by
JosephStyons in
this thread on Stack Overflow. Joseph's script parses the name into Title, First, Middle, and Last. For my purposes, I only needed the first name and so was able to simplify the script and breaking it out into a function makes it easy for us to reuse in a variety of places.
CREATE FUNCTION GetFirstNameFromFullName
(
-- Add the parameters for the function here
@FULL_NAME varchar(255)
)
RETURNS varchar(60)
AS
BEGIN
-- Declare the return variable here
DECLARE @ResultVar varchar(60)
SELECT
@ResultVar = CASE WHEN len(First_NAME.FIRST_NAME) > 1 THEN upper(left(FIRST_NAME.FIRST_NAME, 1)) + lower(right(FIRST_NAME.FIRST_NAME, len(FIRST_NAME.FIRST_NAME) - 1))
ELSE FIRST_NAME.FIRST_NAME END
FROM
(
SELECT
CASE WHEN 0 = CHARINDEX(' ',TITLE.REST_OF_NAME)
THEN TITLE.REST_OF_NAME --No space? return the whole thing
ELSE SUBSTRING(
TITLE.REST_OF_NAME
,1
,CHARINDEX(' ',TITLE.REST_OF_NAME)-1
)
END AS FIRST_NAME
FROM
(
SELECT
CASE WHEN SUBSTRING(TEST_DATA.FULL_NAME,1,3) IN ('MR ','MS ','DR ','MRS', 'MR.', 'MS.', 'DR.')
THEN LTRIM(RTRIM(SUBSTRING(TEST_DATA.FULL_NAME,4,LEN(TEST_DATA.FULL_NAME))))
ELSE LTRIM(RTRIM(TEST_DATA.FULL_NAME))
END AS REST_OF_NAME
FROM
(
SELECT
--trim leading & trailing spaces before trying to process
--disallow extra spaces *within* the name
REPLACE(REPLACE(LTRIM(RTRIM(@FULL_NAME)),' ',' '),' ',' ') AS FULL_NAME
) TEST_DATA
) TITLE
) FIRST_NAME
-- Return the result of the function
RETURN @ResultVar
END
GO