sql server 2008 - How do I trim out middle initial from a person's full name in SQL? -

I am looking for the easiest way to split initial starting holes through the initial and last names. The current layout of the area is [last name], [first name] [MI]. Also, the Middle Initial is not always there. My current code is below, I'm not sure how to trim the middle start with the first name without writing the case statement.

  SE.NET, CHARINDEX (',', H. Name, 0) + 2, LN (H. Nom) - Charundex (',', H. Name, 0 ) 0) As the last name of the sub -strings (H. name, 0, chrinthx (',', h, name, 0)) as the last name, as the last name  

< P>

I have given some assumptions below:

1 - The first names are always longer than one character.
2 - The middle inital will always be a place before.
3 - Data is sorted

If any one is not right then this code will return NULL . If your data is not trimmed, you can use RTRIM at all the instances of @ n below to reduce.

  as declar @n varchar (50) set @n = 'Smith, John A' select @n, case when SUBSTRING (@n, ln (@n) -1 , 1) = '' then SUBSTRING (@n, lan (n), 1) end