SQL server separating first name, last name, and middle initial permanently -

I have a table with the first name, last name, and middle initial, when there is one, a column like this In some names Middle is not initial and I just get rid of those who do that.

  Name two, John B. Smith, Bob  

. So far I have this question:

  SELECT [table]. [Name], LEFT ([table]. [Name], CHARINDEX (',', [table]. [Name]] [name]), LN ([table]. [Name]), ',' [name] AS [first name] FOR [TABLE]  <[SUBSTRING ([table]] [name], CHARINDEX (',', [table], '') [1] AS [ / Pre> 
 gives me the result: 
 
  surname first name john b smith bob  
 
 Is there anything that I have from middle initial Can I add to get rid of this when it happens? Besides, can I change this data permanently? , Then it remains in two different columns? 
 

query:

For this clarity, a CTE And it is also included for many special cases including suffixes (Junior, Senior, Isaac, etc.). .