How to split a string RAJA MD,ELLIOTT H into RAJA as First name, MD as TITLE, ELLIOTT as Last name and H as middle name in SQL server

Question

I am extracting the data from a file and need to split full name into first name, middle name, last name, and title columns.

I have tried LEFT and RIGHT functions

[sql - how to split fullname into first and last name](https://stackoverflow.com/questions/41462307/sql-how-to-split-fullname-into-first-and-last-name) — Lukasz Szozda, Jul 27 '19 at 11:04

score 0 · Answer 1 · answered Jul 27 '19 at 12:53

This is a slippery slope, and there are bound to be surprises/adjustments. That said, perhaps this will do some of the heavy lifting for you.

CROSS APPLY B will split the string on the comma (required and expecting only one)

CROSS APPLY C will split B's Pos1 on the space

CROSS APPLY D will split B's Pos2 on the space

Then it becomes a small matter of looking for patterns

Example

Declare @YourTable Table ([ID] varchar(50),[FullName] varchar(50))
Insert Into @YourTable Values 
 (1,'Raja MD, Elliott H')
,(2,'Jones, David')
,(3,'Smith PhD, Mary Ellen')
,(4,'Allen Franks MD, Richard R.')

Select A.* 
      ,FName=D.Pos1 + case when len(D.Pos2) > 2 Then ' '+D.Pos2 else '' end
      ,MName=case when len(D.Pos2)<3 then D.Pos2 else '' end
      ,LName=case when C.Pos3 is null then C.Pos1 else C.Pos1+' '+C.Pos2 end
      ,Title=case when C.Pos3 is null then IsNull(C.Pos2,'') else C.Pos3 end
 From @YourTable A
 Cross Apply [dbo].[tvf-Str-Parse-Row](FullName,',') B
 Cross Apply [dbo].[tvf-Str-Parse-Row](B.Pos1  ,' ') C
 Cross Apply [dbo].[tvf-Str-Parse-Row](B.Pos2  ,' ') D

Returns

ID  FullName                     FName      MName   LName           Title
1   Raja MD, Elliott H           Elliott    H       Raja            MD
2   Jones, David                 David              Jones   
3   Smith PhD, Mary Ellen        Mary Ellen         Smith           PhD
4   Allen Franks MD, Richard R.  Richard    R.      Allen Franks    MD

The TVF if Interested

CREATE FUNCTION [dbo].[tvf-Str-Parse-Row] (@String varchar(max),@Delimiter varchar(10))
Returns Table 
As
Return (
    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)')))
          ,Pos5 = ltrim(rtrim(xDim.value('/x[5]','varchar(max)')))
          ,Pos6 = ltrim(rtrim(xDim.value('/x[6]','varchar(max)')))
          ,Pos7 = ltrim(rtrim(xDim.value('/x[7]','varchar(max)')))
          ,Pos8 = ltrim(rtrim(xDim.value('/x[8]','varchar(max)')))
          ,Pos9 = ltrim(rtrim(xDim.value('/x[9]','varchar(max)')))
    From  ( values (cast('<x>' + replace((Select replace(@String,@Delimiter,'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml)))  A(xDim)
)

How to split a string RAJA MD,ELLIOTT H into RAJA as First name, MD as TITLE, ELLIOTT as Last name and H as middle name in SQL server

1 Answers1