CODEWITHSUNDEEP
sqlsql-server
M Haase
M Haase

Reputation: 87

Separate name and title into separate columns using SQL Server

I have SQL Server code constructed that will extract the first name, last name, middle initial, and title as separate items from a table which works fine but I run into a problem when there is no middle initial:

Working:

DOE,JOHN A MD - 

Last name: DOE
First name: JOHN
Middle initial:A
Title: MD

Not working:

DOE,JOHN MD -

Last name: DOE
First name: JOHN
Middle initial: M
Title: D

The code I'm currently using:

ISNULL(CASE  
          WHEN 0 = CHARINDEX(' ', RIGHT(Name, LEN(Name), CHARINDEX(',', Name))) 
             THEN LTRIM(SUBSTRING(Name, CHARINDEX(',', Name) + 1, 99)) 
          ELSE SUBSTRING(RIGHT(Name, LEN(Name) - CHARINDEX(',', Name)), 1, CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(',', Name))) - 1)
       END, '') AS FirstName,
ISNULL(CASE 
          WHEN 0 = CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(',', Name))) 
             THEN NULL 
             ELSE SUBSTRING(RIGHT(Name, LEN(Name) - CHARINDEX(',', Name)), CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(',', Name))) + 1, 1) 
       END,'') as MiddleInitial,
ISNULL(CASE 
          WHEN 0 = CHARINDEX(' ', SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1)) 
             THEN SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1) 
             ELSE SUBSTRING(SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1), 1, CHARINDEX(' ', SUBSTRING(Name, 1, CHARINDEX(',', Name) - 1)) - 1) 
       END,'') as LastName,
ISNULL(CASE 
          WHEN 0 = CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(',', Name))) 
             THEN NULL 
             ELSE SUBSTRING(RIGHT(Name, LEN(Name) - CHARINDEX(',', Name)), CHARINDEX(' ', RIGHT(Name, LEN(Name) - CHARINDEX(',', Name))) + 2, 4)
       END,'') as Spec,
ISNULL(ProviderID, ' ') as provid,
ISNULL(ServiceID,' ') as svcid,
ISNULL(SpecialtyAbsServiceID,' ') sabsid

Any help would be greatly appreciated!

Thank you - Matt

Upvotes: 1

Views: 444

Answers (1)

John Cappelletti
John Cappelletti

Reputation: 82010

It is a slippery slope parsing names, but this may help.

It works on the sample data, but multi-word names will be an issue. For example Betty Jane as a First Name

Declare @YourTable table (ID int,Name varchar(50))
Insert Into @YourTable values
(1,'DOE,JOHN A MD'),
(2,'DOE,JOHN MD')

Select A.*
      ,Last_Name   = B.Pos1
      ,First_Name  = B.Pos2
      ,Middle_Name = case when B.Pos4 is null then '' else B.Pos3 end
      ,Title       = case when B.Pos4 is null then B.Pos3 else B.Pos4 end
 From @YourTable A
 Cross Apply (
                Select Pos1 = ltrim(rtrim(xDim.value('/x[1]','varchar(max)')))
                      ,Pos2 = ltrim(rtrim(xDim.value('/x[2]','varchar(max)')))
                      ,Pos3 = ltrim(rtrim(xDim.value('/x[3]','varchar(max)')))
                      ,Pos4 = ltrim(rtrim(xDim.value('/x[4]','varchar(max)')))
                From  (Select Cast('<x>' + replace((Select Replace(Replace(A.Name,',',' '),'  ',' ') as [*] For XML Path('')),' ','</x><x>')+'</x>' as xml) as xDim) as A 
             ) B

Returns

ID  Name            Last_Name   First_Name  Middle_Name Title
1   DOE,JOHN A MD   DOE         JOHN        A           MD
2   DOE,JOHN MD     DOE         JOHN                    MD

Upvotes: 2

Related Questions