SQL simple -> Order list of names by second word

Suppose that we have list of employees names in witch first name and family name are concatenated, like this: 'John Red'. We want to present this list ordered by the family name, that is always appear as a second word in the string. We want that 'John Red' would came after 'Anna Black', but before 'Ed White'. To do this we will need first to eliminate the first word of the string and then simply [ORDER BY ...] remaining part.

-- find first occurrence of blank space in the string, using CHARINDEX function

SELECT EmpName, CHARINDEX(' ',EmpName) AS Pos
FROM Employees1;

The query returns results like these:
EmpNamePos
john silver5
gret garbo5
le chen3
daniel defo7
......


Get part of the string after the blank space:
-- cut of second word using SUBSTRING function
SELECT SUBSTRING(EmpName, CHARINDEX(' ',EmpName),
  LEN(EmpName) - CHARINDEX(' ',EmpName) +1)
FROM Employees1;


Final select with ordered list of names:
SELECT EmpName,
  SUBSTRING(EmpName, CHARINDEX(' ',EmpName),
  LEN(EmpName) - CHARINDEX(' ',EmpName) +1) AS word2
FROM Employees1
ORDER BY SUBSTRING(EmpName,CHARINDEX(' ',EmpName), LEN(EmpName) - CHARINDEX(' ',EmpName) +1);


The query returns reults like these:
EmpNameword2
famke bacherbacher
le chenchen
daniel defodefo
dona fiberfiber
......


MS-Access sql syntax for this query will be:
SELECT EmpName,
  MID(EmpName,INSTR(EmpName,' '),
  LEN(EmpName)-INSTR(EmpName,' ')+1) AS word2
FROM Employees1
ORDER BY MID(EmpName,INSTR(EmpName,' '),
  LEN(EmpName)-INSTR(EmpName,' ')+1)




sqlexamples.info