DotNetNuke, ASP.NET, Web Development Blog

SQL Function to Parse First Name from Full Name

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

Comments

Gravatar
Guy Says:
9/1/2015 1:44:20 PM
Super useful set of tbeals and I'm glad that you took the time to put these together.It's a wonder that Microsoft doesn't document any of this on MSDN or provide even the useful links that you've provided here. Chuck
Leave a Comment
Gravatar
Name:
Email: (not displayed)
Comments:

CAPTCHA image
Enter the code shown above:
Website

Return to previous page