I did it again!

 And I'm going to bore you to death again (sorry about that). I finally figured out how to take LastName, FirstName MiddleNmame and turn it into 3 different columns:  LastName   FirstName   MiddleName.  I know it looks exactly the same as what I did 2 posts ago, but that one did not have a space after the comma, and this one does.  No big thing, except it is.  And I'm going to share the code.  Because it's probably even better than the last one.

SELECT CASE WHEN 0 = CHARINDEX(',',TABLE.FIELD) 
THEN TABLE.FIELD --if there's no comma, it's a last name 
WHEN LEN(SUBSTRING(TABLE.FIELD,(CHARINDEX(' ',TABLE.FIELD)+1),LEN(TABLE.FIELD))) > LEN(SUBSTRING(TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+1),LEN(TABLE.FIELD))) 
THEN SUBSTRING(TABLE.FIELD,1,CHARINDEX(',',TABLE.FIELD)-1) 
ELSE SUBSTRING (TABLE.FIELD,1,CHARINDEX(',',TABLE.FIELD)-1)
END AS LAST_NAME 
,CASE WHEN 0 = CHARINDEX(',',TABLE.FIELD) 
THEN ' ' --IF THERE'S NO COMMA, IT'S A LAST NAME 
WHEN LEN(SUBSTRING(TABLE.FIELD,CHARINDEX(' ',TABLE.FIELD),LEN(TABLE.FIELD)))-(LEN(SUBSTRING(TABLE.FIELD,(CHARINDEX(' ',TABLE.FIELD,(CHARINDEX
(',',TABLE.FIELD)+2))),LEN(TABLE.FIELD)))) > 0 
THEN SUBSTRING(TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+2),(LEN(SUBSTRING(TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+1),LEN(TABLE.FIELD)))-(LEN(SUBSTRING(TABLE.FIELD,(CHARINDEX(' ',TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+2))),LEN(TABLE.FIELD)))))) 
ELSE SUBSTRING(TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+2),(LEN(TABLE.FIELD)))
END AS FIRST_NAME
 ,CASE WHEN 0 = CHARINDEX(',',TABLE.FIELD) 
THEN ' ' --IF THERE'S NO COMMA, IT'S A LAST NAME
WHEN (CHARINDEX(' ',TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+2))) > 0 
THEN SUBSTRING(TABLE.FIELD,(CHARINDEX(' ',TABLE.FIELD,(CHARINDEX(',',TABLE.FIELD)+2))),LEN(TABLE.FIELD))
 ELSE ' ' 
END AS MIDDLE_NAME 
FROM TABLE