Jump to content


ORDER BY the SECOND word in a field

  • Please log in to reply
3 replies to this topic

#1 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
"the first character after the first space in " FULLNAME

Any ideas?

#2 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

  • 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

  • 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