جدا کردن رشته بین دو کاراکتر جدا کننده در SQL Server

DECLARE @temp table(id int, name varchar(50))
insert into @temp(id,name)
values(1,'Baran,Bozorgmehr'),
(2,'Hami,Raad'),
(3,'Nima,Sadr')

Select id
,LEFT(name, CHARINDEX(',', name) - 1) AS Firstname
,REPLACE(SUBSTRING(name, CHARINDEX(',', name), LEN(name)), ',', '') AS Lastname
from @temp