sql - How To Split full Name into First Name, Middle Name, Last Name and suffix in TSQL

Question

For E.g I have a FullName Column in table

FullName 
------------------
Smith Johns Sr
James Macoy
Krushit J Patel II
Sheldon Devid
Jeff vandorf Jr
Steve Smith I

And I want to Result Like

|FirstName | Middle Name | lastName | Suffix |
|--------------------------------------------|
|Smith     | NULL        | Johns    | Null   |
|James     | NULL        | Macoy    | Null   |
|Krushit   | J           | Patel    | II     |
|Sheldon   | NULL        | Devid    | Null   |
|Jeff      | Null        | vandorf  |Jr      |
|Steve     |Smith        | Ronder   |I       |

Answer 1

As others have commented, the question is too vague (and the problem described too complex) to be able to give a particularly helpful answer, but I will try anyway.

We can propose a solution if we make some assumptions about the name values you're wanting to split:

• Each name contains between 2 and 4 "words", each separated by a single space
• 2 word names are formed: [First Name] [Last Name]
• 3 word names are formed: [First Name] [Last Name] [Suffix]
• 4 word names are formed: [First Name] [Middle Name] [Last Name] [Suffix]

In this case, we could solve as follows (if our names exist in a table called names with a single column called Name:

SELECT      
 SUBSTRING(Name, 1, CHARINDEX(' ', Name) - 1) AS FirstName
,CASE LEN(Name) - LEN(REPLACE(Name, ' ', '')) + 1
    WHEN 2 THEN NULL
    WHEN 3 THEN NULL
    WHEN 4 THEN SUBSTRING(RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name)), 1, CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))) - 1)
 END AS [Middle Name]
,CASE LEN(Name) - LEN(REPLACE(Name, ' ', '')) + 1
    WHEN 2 THEN RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))
    WHEN 3 THEN SUBSTRING(RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name)), 1, CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name))) - 1)
    WHEN 4 THEN SUBSTRING(RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name, CHARINDEX(' ', Name) + 1)), 1, CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(' ', Name, CHARINDEX(' ', Name) + 1))) - 1)
 END AS lastName
,CASE LEN(Name) - LEN(REPLACE(Name, ' ', '')) + 1
    WHEN 2 THEN NULL
    WHEN 3 THEN REVERSE(SUBSTRING(REVERSE(Name), 1, CHARINDEX(' ', REVERSE(Name)) - 1))
    WHEN 4 THEN REVERSE(SUBSTRING(REVERSE(Name), 1, CHARINDEX(' ', REVERSE(Name)) - 1))
 END AS Suffix
FROM names

This is not the most elegant solution, but it illustrates the usage of CHARINDEX and SUBSTRING that can be applied to break down a string like this. There is definitely some redundancy which could be worked out of this query and more elegant ways to implement it (plus it may not suit your dataset because of the assumptions above), but hopefully it's a helpful starting point for you.

A neater solution might be to create a function which takes 2 parameters - a string and an integer to indicate which "word" you wish to return from that string. You could then call this function from within similar CASE logic to return the first, second, third or fourth word in the name as required.

If you need to be able to handle 3 word names of the form [First Name] [Middle Name] [Last Name] (as I suspect you do), you'll probably want to build a list of the possible suffixes and use that list to determine whether each 3 word name has a suffix or middle name accordingly.