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

I don't think you can do this automatically and reliably. For example, how would you automatically tell that Juan Carlos Perez goes on P and Richard Milhous Nixon goes on N?

You'll have to use heuristics and a dictionary of names or something similar, and that will always fail on some cases. It's best to do what Juliet says: process the data to generate candidates for review, and separate the name column into three (or four) columns: FirstName, MiddleInitial, LastName.

Answer 2

If you really want to do it, how about

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