> -----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]
