Jump to content


Photo

ORDER BY the SECOND word in a field


  • Please log in to reply
3 replies to this topic

#1 scampisi

scampisi
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 04 March 2006 - 11:42 PM

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
firstname lastname
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?



#2 SpectralDesign.Net

SpectralDesign.Net
  • Members
  • PipPip
  • Member
  • 15 posts
  • LocationToronto, Ontario

Posted 05 March 2006 - 01:10 AM

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.


#3 wickning1

wickning1
  • Members
  • PipPipPip
  • Advanced Member
  • 405 posts

Posted 05 March 2006 - 01:38 AM

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

SELECT * from catalog ORDER BY SUBSTR(LTRIM(fullname), LOCATE(' ',LTRIM(fullname)))


#4 scampisi

scampisi
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 06 March 2006 - 01:07 AM

Thanks a million. That worked great.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users