Here's some basic sample code that should get you going.
You'll have to account for edge cases like missing middle initials, extra spaces, prefixes/suffixes, etc...


DECLARE @MBR_NM VarChar(50) = 'Smith, John J'

SELECT
    -- Find the position of the first comma in the string
    CharIndex(',',@MBR_NM) AS POSITION_FIRST_COMMA,

    -- Find the second space in the string.
    -- The third parameter of CharIndex lets you specify a starting position.
    -- Look for a space, but starting after the position of your comma
    -- plus a couple of characters to get past the first space.
    CharIndex(' ', @MBR_NM, CharIndex(',',@MBR_NM) + 3) AS POSITION_SECOND_SPACE,

    /*
        Now combine the two above formulas to break out the pieces you need.
    */

    -- First name is everything left of the comma
    Left(@MBR_NM, CharIndex(',',@MBR_NM) -1 ) AS Name_Last,

    -- Middle name is between the space after the comma and the second space
    SubString(@MBR_NM, CharIndex(',',@MBR_NM) + 2, CharIndex(' ', @MBR_NM, CharIndex(',',@MBR_NM) + 3) - (CharIndex(',',@MBR_NM) + 2) ) AS Name_First,

    -- Middle initial is one character after the second space
    SubString(@MBR_NM, CharIndex(' ', @MBR_NM, CharIndex(',',@MBR_NM) + 3) +1, 1) AS Name_Middle_Initial