Sort mid field

StacyO

I need to do a sort on the second word of a field -- is this possible?

Thanks in advance,
StacyO

ultraslacker

Yes you can - with mysql it would involve a nesting of substring and position functions in the ORDER BY clause. You could also write your own function for this. Read up on the string functions in the mysql manual.

StacyO

Sorry to be dense, but I did read what you suggested and don't feel any closer to the solution.

Specifically, the database I am accessing has only one field for a person's name (first and last). I'm trying to do an ORDER BY on their last names, which is the second word in the field. Can you give me a hint as to the nested string function that I would use?

Thanks again,
StacyO

xblue

Hi StacyO,

just wondering: did you consider records where there may be more than one first name, or do you disallow that right away?

to get around these kind of problems, it may be worth a thought to split this column into 2, one for the first name and one for the last, and sorting would be really easy with this approach too.

StacyO

I have thought about that -- and completely agree. I've asked our database guy to change it to two fields, it's just the right way to do it. I'm just trying to be prepared if it doesn't happen...

best,
StacyO

xblue

maybe it could be done with mysql regex. can't test it right now, but sorting by a regex that matches a space followed by a sequence of letters might work.
http://www.mysql.com/doc/en/Regexp.html

however, it is not really efficient, and in the end your database guy might do the necessary changes more quickly if you tell him what you want to do 😉