ORDER BY the SECOND word in a field

[scampisi]

I have a situation where I am needing to sort a list alphabetically by the last name. However, due to BAD planning, all of the names are stored in a single field like this
[i]firstname lastname[/i]
What is going to be my best way to sort my list? I looked through string manipulation in MYSQL, but I couldn't find a way to doa select statment that would search through my table, and pull all names based on the lastname.
SELECT * from catalog ORDER BY . . .
and here I want to say
ORDER BY
"the first character after the first space in " FULLNAME

Any ideas?

[SpectralDesign.Net]

I can't give you a straight answer, but I see that you can use regexp in MySQL... here's just one page I found, but it should be a good starting point....

[a href=\"http://www.tech-recipes.com/mysql_tips484.html\" target=\"_blank\"]one page discussing regular expressions in MySQL selects[/a]

I'd highly recommend exporting the data, and re-importing it into two tables though, because it'll save you time down the road when any similar situations arise.

[wickning1]

This will sort based on the second word (won't catch middle names correctly).

[code]SELECT * from catalog ORDER BY SUBSTR(LTRIM(fullname), LOCATE(' ',LTRIM(fullname)))[/code]

[scampisi]

Thanks a million. That worked great.