Mike Marshall Mike Marshall - 4 months ago 23
SQL Question

T-SQL Function to Parse / Split First and Last name from a String

I could use some help creating a function that will parse or split the first name of a string as its own value. I have code that will do this but don't quite have the skills to covert this to a Function. Here is the code I have that has been tested and works fine on its own:

SELECT
CASE WHEN 0 = CHARINDEX(' ',StageThree.REST_OF_NAME)
THEN StageThree.REST_OF_NAME --No space? return the whole thing
ELSE SUBSTRING(StageThree.REST_OF_NAME, 1, CHARINDEX(' ',StageThree.REST_OF_NAME)-1)
END AS FirstName

FROM
(SELECT
--if the first three characters are in this list,
--then pull it as a "StageThree". otherwise return NULL for StageThree.
CASE WHEN SUBSTRING(StageTwo.FullName,1,3) IN ('MR ','MS ','DR ','MRS')
THEN LTRIM(RTRIM(SUBSTRING(StageTwo.FullName,1,3)))
ELSE NULL
END AS Title

--if you change the list, don't forget to change it here, too.
--so much for the DRY prinicple...
,CASE WHEN SUBSTRING(StageTwo.FullName,1,3) IN ('MR ','MS ','DR ','MRS')
THEN LTRIM(RTRIM(SUBSTRING(StageTwo.FullName,4,LEN(StageTwo.FullName))))
ELSE LTRIM(RTRIM(StageTwo.FullName))
END AS REST_OF_NAME
,StageTwo.OriginalName

FROM
(SELECT
--trim leading & trailing spaces before trying to process
--disallow extra spaces *within* the name
REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(Contact)),' ',' '),' ',' '),',',' '),'.',' ') AS FullName
,Contact AS OriginalName
FROM
My.dbo.database
) StageTwo
) StageThree


If anyone could help in converting this to a Function I would greatly appreciate it. I will also post the code to get the last name (as a function) if I can get help with this.

This code appears to be right, but I cannot get it to launch in my query.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[FirstName](@input VARCHAR(100)) --how ever many you need
RETURNS @returnTable table(FirstName varchar(100))

AS

BEGIN
INSERT INTO @returnTable (FirstName)
SELECT

CASE WHEN 0 = CHARINDEX(' ',StageThree.REST_OF_NAME)
THEN StageThree.REST_OF_NAME --No space? return the whole thing
ELSE SUBSTRING(StageThree.REST_OF_NAME, 1, CHARINDEX(' ',StageThree.REST_OF_NAME)-1)
END AS FirstName

FROM
(SELECT
--if the first three characters are in this list,
--then pull it as a "StageThree". otherwise return NULL for StageThree.
CASE WHEN SUBSTRING(StageTwo.FullName,1,3) IN ('MR ','MS ','DR ','MRS')
THEN LTRIM(RTRIM(SUBSTRING(StageTwo.FullName,1,3)))
ELSE NULL
END AS Title

--if you change the list, don't forget to change it here, too.
--so much for the DRY prinicple...
,CASE WHEN SUBSTRING(StageTwo.FullName,1,3) IN ('MR ','MS ','DR ','MRS')
THEN LTRIM(RTRIM(SUBSTRING(StageTwo.FullName,4,LEN(StageTwo.FullName))))
ELSE LTRIM(RTRIM(StageTwo.FullName))
END AS REST_OF_NAME

FROM
(SELECT
--trim leading & trailing spaces before trying to process
--disallow extra spaces *within* the name
REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@Input)),' ',' '),' ',' '),',',' '),'.',' ') AS FullName

) StageTwo
) StageThree
RETURN

Answer

After some more trials I was able to rewrite this as a scalar function, shown below. As promised, I am also posting the code for the Last Name. Neither of these are foolproof, and this is customized for a specific application (hence the term "blank" instead of NULL value). But if you have a situation where you need to move data collected as a single string to a system that requires first and last names, this will work fairly well. If you come up with ideas to overcome some of the other caveats with names please share.

Thanks to scsimon for the help!

 CREATE FUNCTION [dbo].[getFirstName](@input VARCHAR(100)) 
 RETURNS   VARCHAR(100)

 AS

 BEGIN
 DECLARE @Result as Varchar(100)
 SET @Result = (
 SELECT

   CASE WHEN 0 = CHARINDEX(' ',StageThree.REST_OF_NAME)
         THEN StageThree.REST_OF_NAME --No space? return the whole thing
         ELSE SUBSTRING(StageThree.REST_OF_NAME, 1, CHARINDEX(' ',StageThree.REST_OF_NAME)-1)
         END AS FirstName

             FROM
            (SELECT
              --if the first three characters are in this list,
              --then pull it as a "StageThree".  otherwise return NULL for StageThree.
              CASE WHEN SUBSTRING(StageTwo.FullName,1,3) IN ('MR ','MS ','DR ','MRS')
                   THEN LTRIM(RTRIM(SUBSTRING(StageTwo.FullName,1,3)))
                   ELSE NULL
                    END AS Title

  --if you change the list, don't forget to change it here, too.
  --so much for the DRY prinicple...
 ,CASE WHEN SUBSTRING(StageTwo.FullName,1,3) IN ('MR ','MS ','DR ','MRS')
       THEN LTRIM(RTRIM(SUBSTRING(StageTwo.FullName,4,LEN(StageTwo.FullName))))
       ELSE LTRIM(RTRIM(StageTwo.FullName))
       END AS REST_OF_NAME

FROM
  (SELECT
    --trim leading & trailing spaces before trying to process
    --disallow extra spaces *within* the name
    REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@Input)),'  ',' '),'  ',' '),',',' '),'.',' ') AS FullName

  ) StageTwo
) StageThree )
 RETURN @Result
 END

And the LastName Function:

CREATE FUNCTION [dbo].[getLastName](@input VARCHAR(100)) 
RETURNS VARCHAR(100)
AS

BEGIN
 DECLARE @Result as Varchar(100)
 SET @Result = (


SELECT 

   CASE WHEN SUBSTRING(StageFour.REST_OF_NAME, 1 + CHARINDEX(' ',StageFour.REST_OF_NAME),LEN(StageFour.REST_OF_NAME)) IN ('jr','sr','II','III','IV')
        THEN SUBSTRING(StageFour.REST_OF_NAME, 1 , CHARINDEX(' ',StageFour.REST_OF_NAME)-1) + ' ' +
             SUBSTRING(StageFour.REST_OF_NAME, 1 + CHARINDEX(' ',StageFour.REST_OF_NAME),LEN(StageFour.REST_OF_NAME))
        WHEN SUBSTRING(StageFour.REST_OF_NAME, 1 + CHARINDEX(' ',StageFour.REST_OF_NAME),LEN(StageFour.REST_OF_NAME)) IS NULL THEN '(BLANK)'
        ELSE SUBSTRING(StageFour.REST_OF_NAME, 1 + CHARINDEX(' ',StageFour.REST_OF_NAME),LEN(StageFour.REST_OF_NAME))
        END AS LastName

 FROM
      (SELECT
        StageThree.Title
       ,CASE WHEN 0 = CHARINDEX(' ',StageThree.REST_OF_NAME)
             THEN StageThree.REST_OF_NAME --No space? return the whole thing
             WHEN StageThree.REST_OF_NAME IS NULL THEN '(Blank)'
             ELSE SUBSTRING(StageThree.REST_OF_NAME, 1, CHARINDEX(' ',StageThree.REST_OF_NAME)-1)
             END AS FirstName
       ,CASE WHEN 0 = CHARINDEX(' ',StageThree.REST_OF_NAME)  
             THEN NULL  --no spaces @ all?  then 1st name is all we have
             ELSE SUBSTRING(StageThree.REST_OF_NAME, CHARINDEX(' ',StageThree.REST_OF_NAME)+1, LEN(StageThree.REST_OF_NAME))
             END AS REST_OF_NAME
       ,StageThree.FullName
                 FROM
                (SELECT
                  --if the first three characters are in this list,
                  --then pull it as a "StageThree".  otherwise return NULL for StageThree.
                  CASE WHEN SUBSTRING(StageTwo.FullName,1,3) IN ('MR ','MS ','DR ','MRS')
                       THEN LTRIM(RTRIM(SUBSTRING(StageTwo.FullName,1,3)))
                       ELSE NULL
        END AS Title

      --if you change the list, don't forget to change it here, too.
      --so much for the DRY prinicple...
     ,CASE WHEN SUBSTRING(StageTwo.FullName,1,3) IN ('MR ','MS ','DR ','MRS')
           THEN LTRIM(RTRIM(SUBSTRING(StageTwo.FullName,4,LEN(StageTwo.FullName))))
           ELSE LTRIM(RTRIM(StageTwo.FullName))
           END AS REST_OF_NAME
     ,StageTwo.FullName
    FROM
      (SELECT
        --trim leading & trailing spaces before trying to process
        --disallow extra spaces *within* the name
        REPLACE(REPLACE(REPLACE(REPLACE(LTRIM(RTRIM(@Input)),'  ',' '),'  ',' '),',',' '),'.',' ') AS FullName

      ) StageTwo
    ) StageThree
  ) StageFour
)

RETURN @Result
END