> -----Original Message----- > From: Emily Heureux [mailto:[EMAIL PROTECTED] > Sent: Friday, June 13, 2008 11:12 AM > To: Boyd, Todd M.; mysql@lists.mysql.com > Subject: RE: natural sort via substrings > > 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.
---8<--- snip Emily, >From an exhaustive search of the web (including MySQL's page and others), it appears that Regular Expression support in MySQL is limited at best. All you can do with it thus far is determine whether or not something matched the given expression--1 or 0 are the only results possible, from what I can gather. You may be forced to either nest a bunch of queries using substring_index() and other server-side string manipulation functions, or sort the data after queried-extraction. :( By all means, though, do not take my information as gospel. There may very well be a way to pull off what you're trying to do... but ORDER BY REGEXP is not it. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
