misscrf (2/3/2016)
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
You should warn that it only works with the following format:
LastName, FirstName MiddleInitial.
If the comma is missing, it might generate errors. If the point for the middle initial is missing, it will give incorrect information. So this is highly dependent on correct format of the name which might be useless in many cases.