Seperating a name in sql using substring

Question

I've been trying to separate a middle name from a first name using the substring function in sql. They are separated by a space. I was able to separate the last name which was separated by a comma and stored in a new table using...

SELECT employeeName, 
        SUBSTRING(employeeName, 1, CHARINDEX(',', employeeName)-1) AS last_name,
        SUBSTRING(employeeName, CHARINDEX(',', employeeName)+1, LEN(employeeName)) AS  first_name
    INTO newTable
    FROM oldTable

The middle name stayed attached to the first name and I need it separated for data searching purposes.

Here is what I am trying...

SELECT employeeName, first_name, last_name,
    SUBSTRING(first_name, 1, CHARINDEX(' ', first_name)-1) AS amiddle_name
INTO finalTable
FROM oldTable