I found this post in trying to split a full name with first, middle and last. I came up with this solution and thought I would share, in case it can help someone else:

,CASE WHEN LTRIM(RIGHT(@fullname,(len(@fullname)-charindex(',',@fullname)))) LIKE '%.%'

THEN REPLACE(LTRIM(RIGHT(@fullname,(len(@fullname)-charindex(',',@fullname)))),REVERSE(left(REVERSE(@fullname), charindex(' ',REVERSE(@fullname))-1)),'')

ELSE LTRIM(RIGHT(@fullname,(len(@fullname)-charindex(',',@fullname)))) END AS First

,ISNULL(CASE WHEN RIGHT(REVERSE(left(REVERSE(@fullname), charindex(' ',REVERSE(@fullname))-1)),1) = '.'

THEN REVERSE(left(REVERSE(@fullname), charindex(' ',REVERSE(@fullname))-1)) END,'') AS Middle

,LTRIM(RTRIM(LEFT(@fullname, CHARINDEX(',', @fullname)-1))) AS Last