Watch this example on YouTube:
Here is my table:
The following statement
select CustomerName,
Substring(CustomerName, 1, (CHARINDEX(' ', CustomerName + ' ')-1)) as FirstName,
Substring(CustomerName, LEN(Substring(CustomerName, 1, (CHARINDEX(' ', CustomerName + ' ')+1))), LEN(CustomerName)) as LastName
from Table3
will produce this result: