[RESOLVED] Made an SQL mistake...

[Studio381]

... by not splitting database member names into separate fields for firstname and last name. ie keeping the complete name in a single field.
Now I want to be able to display all the members of the database in alphabetical order based on surname.

Any good ideas how to do this without redoing the application and changing the name field into 2 variables?

One possibility I had thought about was to change the name when it is added to the DB eg from 'John Smith' to 'Smith John' and store as 'Smith John'. This would allow a SELECT * ... ORDER BY to sort in alphabetical order and then I could change the name back from 'Smith John' to 'John Smith' when output.

Is this just asking for more trouble and is there any string function to do this ?

Or better still a better way to do this.

Thanks.

[maxpup979]

I believe you should just be able to order by substr--

[NogDog]

SELECT 
   col1,
   col2,
   <etc....>,
   SUBSTRING_INDEX(`name`, ' ', 1) AS `first_name`,
   SUBSTRING_INDEX(`name`, ' ', -1) AS `last_name`
FROM `table_name`
ORDER BY `last_name`, `first_name`