Hi, I am sorry for being so vague. The values are not as simple as jane20, jane10, or jane2. There are names like, jane-2, alex 3, alex4, and just 'jane', etc. ORDER BY sorts by either numerical OR string, not both, as far as I can tell. I'll have to think about zero-fill, but I doubt that will work given the lack of standards for the names I am getting (protein names). I need a sort that works like the way a human would sort.
As for substring_index(), I was using that because the names I am dealing with often have a distinguishing number at the end, so I would like to just grab all but the number(s), and then grab just the number(s) (So, actually, I am using substring_index() twice). If I can separate out the numbers at the end from the rest of the string with a regexp delimiter, the problem is solved. I just don't know if that can be done from within ORDER BY. Thanks, Emily > -----Original Message----- > From: Boyd, Todd M. [mailto:[EMAIL PROTECTED] > Sent: Friday, June 13, 2008 8:33 AM > To: Emily Heureux; mysql@lists.mysql.com > Subject: RE: natural sort via substrings > > > -----Original Message----- > > From: Emily Heureux [mailto:[EMAIL PROTECTED] > > Sent: Thursday, June 12, 2008 8:25 PM > > To: mysql@lists.mysql.com > > Subject: natural sort via substrings > > > > Hi, I am attempting to do a "natural sort" from within mysql, if > > possible. > > So, for example, jane2 would come before jane10, and normal strings > > would > > still sort as expected. I found some solutions, like using length for > > the > > numerical part, but that only works if the strings are the same > length. > > Ideally, I would like to use substring_index, but stick a regexp in as > > the > > delimiter. So far, it seems you cannot do this. Does anyone know how > > to > > put a regexp as the delimiter in substring_index? For example, I want > > to do > > something like this: > > > > .order by substring_index(name, 'regexp [0-9]+', 1); > > > > Is this possible? > > Forgive me if I am incorrect, but wouldn't "jane2" already be listed > before "jane10" if you just ORDER BY <fieldname> ASC? I suppose "jane2" > and "jane20" would wind up next to each other if this were the case, but > can you not zero-fill your values (i.e., "jane02") to prevent this from > happening? > > Sorry if my suggestion falls short of the mark, but the conditions for > your test case were vague at best. :) Can you not zero-fill? Are you > sorting by the entire field's value, or just a portion of it? Wouldn't > substring_index() sort all "jane##" entries arbitrarily, since your > (theoretical) example returns everything to the left of the first match > (but not including the match)? > > > Todd Boyd > Web Programmer > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
