Sorting by "last name" in MySQL when "full name" has periods in it

Question

I found this nifty little order by condition that sorts strings of the type "First Last" nicely, even handling "First Van Damn" properly.

"SUBSTRING(p.name, LOCATE(' ', p.name) +1)

Now, I have some names in there like "Alfred E. Newman" and want the sorting to work properly for that name (ie it does not end up under E).

Any help would be greatly appreciated.

Answer 1

If you really want to do it, how about

RIGHT(p.name, LOCATE(' ', REVERSE(p.name)) - 1)