I have mainly Oracle and MS*SQL experience, no MySQL, but looking at the docs online for string functions you should be able to use LOCATE() or INSTR() to do this. Look for the first occurance of a substring of ' ' in Name (assuming you only have first and last name - no middle names - and you always have at least 1 space in the name and name is of format 'firstname lastname') and then sort on everything to the right of the first space similar to:

SELECT *
FROM table
WHERE condition(s)
ORDER by RIGHT(CHAR_LENGTH(Name) - LOCATE(' ', Name))

Unfortunately, I can't test it because I don't have MySQL. You may need to play around with using SUBSTRING() instead of RIGHT, LENGTH() instead of CHAR_LENGTH, INSTR() instead of LOCATE(). I'm not sure if you're using multibyte characters or not as well.

But essentially for 'Dick Van Dike'.

total length = 13
position of first ' ' is 5

So if you grab the rightmost 8 characters (13-5) you should get 'Van Dike'.

Another way might be to locate the first ' ' then use SUBSTRING() to get all characters to the right of the first ' ' similar to the following:

SELECT *
FROM table
WHERE condition(s)
ORDER BY SUBSTING(Name, LOCATE(' ', Name)+1).

This approach would involve only 2 function calls instead of 3.

Hope this gets you thinking on the right track. I'm sure there are many more ways to 'skin' this cat. Sorry I couldn't test it for you to give you an exact solution.

Thank you so much for your reply ZydoSEO. The second option that you suggested appears to work wonderfully. I did not have a chance to try the first option you gave, but for anyone else that might stumble across this situation, "Van Dyke" is sorted where it should be (with the "V"s) using:
SELECT *
FROM table
WHERE condition(s)
ORDER BY SUBSTING(Name, LOCATE(' ', Name)+1).

Thanks again ZydoSEO!