Order By Surname in a Table

select * from EmployeeDetail

Id EmployeeName
1 Anurag Nayak
2  Ritika mohanty
3     Siba Dalai
4 Biswa Panda

Trick 1 ;with cte(id,name,finalIndex,startIndex)as
(
select id,
EmployeeName,
LEN(EmployeeName) as LengthName,
CHARINDEX(' ',EmployeeName) as StartIndex from dbo.EmployeeDetail
)
select id,name,SUBSTRING(name,startIndex+1,finalIndex) as final from cte 
order by final


id       name                               final
3 Siba Dalai                       Dalai
2 Ritika mohanty
1 Anurag Nayak                Nayak
4 Biswa Panda                   Panda



Trick 2

select EmployeeName
from employeedetail
order by REVERSE(substring(reverse(EmployeeName),1,CHARINDEX(' ',reverse(EmployeeName))))