I've been trying to implement the solution to this post with the first delimiter being a comma, but I'm only getting two characters of the first name and no middle name.
The code I'm using is:
SELECT DISTINCT Instr1_Name,
LEFT(Instr1_Name, CHARINDEX(',', Instr1_Name + ' ') - 1) AS LName,
e, CHARINDEX(',', Instr1_Name + ' ') + 1,
CHARINDEX(' ', Instr1_Name + ' ', CHARINDEX(' ', Instr1_Name + ' ') + 1) -
CHARINDEX(' ', Instr1_Name + ' '))) AS FName,
SUBSTRING(Instr1_Name, CHARINDEX(' ', Instr1_Name + ' ',
CHARINDEX(' ', Instr1_Name + ' ') + 1) + 1, LEN(Instr1_Name)) AS Mname
INTO #TempFac FROM SemesterCoursesImport
LEFT JOIN [Faculty Info]
r1_EmplID = [Faculty Info].EmplID
WHERE [Faculty Info].EmplID Is Null
r1_EmplID Is Not Null
sterPS = '2131'
The results I'm getting with the above statement are attached. What do I need to do to change this statement to get the correct first and middle name? The nested CharIndex commands are confusing. I'll give extra points if you can explain in English how the substring and charindex are used to get the correct result. In particular, I don't understand why ' ' is added to the end of the Instr1_name.