Split full Name into First Name, Middle Name, Last Name and suffix

Question

For example I have a FullName column in a table

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

And I want a 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

SELECT 

d.First_Name


,CASE WHEN 0 = CHARINDEX(' ',d.REST_OF_NAME)
       THEN NULL  
       ELSE SUBSTRING(                                                    ---- finds the middle name from rest of the name 
                       d.REST_OF_NAME
                      ,1
                      ,CHARINDEX(' ',d.REST_OF_NAME)-1
                     )
       END AS Middle_Name

,SUBSTRING(
             d.REST_OF_NAME                                             ---- finds the Last name from rest of the name 
            ,1 + CHARINDEX(' ', d.REST_OF_NAME)
            ,LEN( d.REST_OF_NAME)
           ) AS Last_Name

,d.Suffix
,d.CUSTOMER_NUMBER
,D.Orignal_Data_String
from
(SELECT c.Suffix,

CASE WHEN 0 = CHARINDEX(' ',c.Remainding_Name_Part)
         THEN c.Remainding_Name_Part 
         ELSE SUBSTRING(                                                    ---- substring first name fro rest of the name from reminding part of the name 
                         c.Remainding_Name_Part
                        ,1
                        ,CHARINDEX(' ',c.Remainding_Name_Part)-1
                       )
    END AS First_Name
,CASE WHEN 0 = CHARINDEX(' ',c.Remainding_Name_Part)  
         THEN NULL  
         ELSE SUBSTRING(
                         c.Remainding_Name_Part
                        ,CHARINDEX(' ',c.Remainding_Name_Part)+1           ------    substring rest of the name after substracting firstname from the remainding partof the name
                        ,LEN(c.Remainding_Name_Part)
                       )
    END AS REST_OF_NAME

,c.CUSTOMER_NUMBER
,C.Orignal_Data_String
FROM 
(SELECT 
CASE WHEN RIGHT(b.Name,2) IN ('IV','Jr','Sr')
     THEN LTRIM(RTRIM(RIGHT(b.Name,2)))                                     ----finds suffix in name        
        WHEN RIGHT(b.Name,3) IN ('III','Esq',' II')
       THEN LTRIM(RTRIM(RIGHT(b.Name,3))) 

ELSE NULL
END AS [Suffix]
,
CASE WHEN RIGHT(b.Name,2) IN ('IV','Jr','Sr')
     THEN LTRIM(RTRIM(LEFT(b.name,LEN(b.name)-2)))                         ----finds remider part of name after subtrecting suffix        
       WHEN RIGHT(b.Name,3) IN ('III',' Esq',' II')
       THEN LTRIM(RTRIM(LEFT(b.name,LEN(b.name)-3)))

ELSE LTRIM(RTRIM(b.name))
END AS [Remainding_Name_Part]
,B.CUSTOMER_NUMBER
,B.Orignal_Data_String


FROM 

(SELECT 
REPLACE(REPLACE(LTRIM(RTRIM(a.NAME)),'  ',' '),'  ',' ') AS [Name]         ------ Clears spaces 
,A.NAME AS [Orignal_Data_String]
,a.CUSTOMER_NUMBER
FROM 
(
SELECT NAME,CUSTOMER_NUMBER                                                 ------ finds the customers
FROM [FIS_CORE_FEEDS_DM].[dbo].[FIS_DAILY_CUST_TABLE]
WHERE CUSTOMER_TYPE !='O'
        )A
     )B
   )C
)D