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

Question

I'm look for the simplest way to split up first and last name wile trimming out the middle initial. The current layout of the field is [Last Name], [First Name] [MI]. Also, middle initial is not always there. My current code is below, I'm just not sure how to trim out the middle initial from first name without writing a case statement.

SELECT SUBSTRING(h.Name, CHARINDEX(',', h.Name, 0) + 2, LEN(h.Name) - CHARINDEX(',', h.Name, 0)), 0 as FirstName
    ,SUBSTRING(h.Name, 0, CHARINDEX(',', h.Name, 0)) as LastName
FROM Members

Answer 1

I have made some assumptions below:

1 - First names are always longer than one character.
2 - Middle inital will always be preceded by a space.
3 - The data is trimmed.

This code will return NULL if any of the above are not true. If your data is not trimmed, you can use RTRIM on all instances of @n below to mitigate.

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

Answer 2

What are the business rules of this system? Will it always be:

last name , first name space a possible middle initial

What other permutations can exist?

Will it always be space letter . ? Because then you could always take the right three characters, look for a space and a period, then remove the set of three.