CODEWITHSUNDEEP
sqlsql-serversql-server-2012
sky_limit
sky_limit

Reputation: 133

Extract last name, first name and suffix into separate columns

I was wondering if someone could provide me an easy way to extract the names into different columns as below. There is a comma after the Last Name and space between First Name, Middle Initial, and Suffix. Greatly appreciate it.

Stored Data:

Name
Walker,James M JR
Smith,Jack P
Smith,Whitney

Required result:

LastName   FirstName   Suffix
Walker     James       JR
Smith      Jack
Smith      Whitney

Tried Code:

select top 5 Name,
LEFT(Name, CHARINDEX(',', Name) - 1) AS LastName,
right(Name, len(Name) - CHARINDEX(',', Name)) as FirstName

Just having problem with separating First Name from Middle Initial and Suffix. Then getting Suffix from the last space from the right.

Upvotes: 2

Views: 5352

Answers (1)

Vladimir Baranov
Vladimir Baranov

Reputation: 32703

You really should store these parts of the name in separate columns (first normal form) to avoid such parsing.

You can put all the logic into one huge call of nested functions, but it is quite handy to separate them into single calls using CROSS APPLY.

The parsing is straight-forward:

  • find position of comma
  • split the string into part before comma (LastName) and part AfterComma
  • find position of first space in the second part AfterComma
  • split the string into two parts again - this gives FirstName and the rest (AfterSpace)
  • find position of space in AfterSpace
  • split the string into two parts again - this gives Initial and Suffix.

The query also checks results of CHARINDEX - it returns 0 if the string is not found.

Obviously, if the string value is not in the expected format, you'll get incorrect result.

DECLARE @T TABLE (Name varchar(8000));
INSERT INTO @T (Name) VALUES
('Walker'),
('Walker,James M JR'),
('Smith,Jack P'),
('Smith,Whitney');

SELECT
    Name
    ,LastName
    ,AfterComma
    ,FirstName
    ,AfterSpace
    ,MidInitial
    ,Suffix
FROM
    @T
    CROSS APPLY (SELECT CHARINDEX(',', Name) AS CommaPosition) AS CA_CP
    CROSS APPLY (SELECT CASE WHEN CommaPosition > 0 THEN 
        LEFT(Name, CommaPosition - 1) ELSE Name END AS LastName) AS CA_LN
    CROSS APPLY (SELECT CASE WHEN CommaPosition > 0 THEN 
        SUBSTRING(Name, CommaPosition + 1, 8000) ELSE '' END AS AfterComma) AS CA_AC

    CROSS APPLY (SELECT CHARINDEX(' ', AfterComma) AS SpacePosition) AS CA_SP
    CROSS APPLY (SELECT CASE WHEN SpacePosition > 0 THEN 
        LEFT(AfterComma, SpacePosition - 1) ELSE AfterComma END AS FirstName) AS CA_FN
    CROSS APPLY (SELECT CASE WHEN SpacePosition > 0 THEN 
        SUBSTRING(AfterComma, SpacePosition + 1, 8000) ELSE '' END AS AfterSpace) AS CA_AS

    CROSS APPLY (SELECT CHARINDEX(' ', AfterSpace) AS Space2Position) AS CA_S2P
    CROSS APPLY (SELECT CASE WHEN Space2Position > 0 THEN 
        LEFT(AfterSpace, Space2Position - 1) ELSE AfterSpace END AS MidInitial) AS CA_MI
    CROSS APPLY (SELECT CASE WHEN Space2Position > 0 THEN 
        SUBSTRING(AfterSpace, Space2Position + 1, 8000) ELSE '' END AS Suffix) AS CA_S

result

Name                 LastName    AfterComma    FirstName    AfterSpace  MidInitial  Suffix
Walker               Walker
Walker,James M JR    Walker      James M JR    James        M JR        M           JR
Smith,Jack P         Smith       Jack P        Jack         P           P
Smith,Whitney        Smith       Whitney       Whitney

Upvotes: 0

Related Questions