Try:
SELECT stdid,
Firstname,
SUBSTRING(Email, 1, CHARINDEX('@', Email,1) - 1) AS Part1,
SUBSTRING(Email, CHARINDEX('@', Email,1), CHARINDEX('.', Email,1) - CHARINDEX('@', Email,1)) AS Part2,
SUBSTRING(Email, CHARINDEX('.', Email,1), 9999) AS Part3
FROM MyTable