From: Jim Lyons [mailto:[EMAIL PROTECTED] Sent: Friday, June 13, 2008 12:10 PM To: Boyd, Todd M. Subject: Re: natural sort via substrings
What I would do is form 2 additional fields from the first, an alphanumeric field and a numeric field so: jane-2 jane- 2 alex 3 alex 3 (maybe put a blank after the 'x' but be sure to handle it properly, trailing spaces are tricky) alex4 alex 4 jane jane (maybe null, or 0 for the numeric field, depending on the application) Then sort on the 2 fields. Depending on your application, you might want to keep the entire name field as well as the 2 derivative fields, just accept the de-normalization. You can use the entire field for display purposes. If your table is of any size whatsoever this would be vastly preferable to doing all sorts of substring-ing in your where clauses. You won't have a prayer of having the optimizer use an index. Jim On Fri, Jun 13, 2008 at 11:58 AM, Boyd, Todd M. <[EMAIL PROTECTED]> wrote: > -----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. ---- Jim, Thank you for formulating my idea into a more easily-understandable format. :) It's been a long week. What I meant to say when I mentioned "sort the data after queried-extraction" was that you would need to split the data extracted with a query into its sort-able parts as you described. Very well put on your part, though. Emily, You might try and find a conditional string function that will find the first digit character in a string (or a nifty manipulation of a different string function that accomplishes the same objective). I understand that's sort of along the line of what you were trying to do in the first place, but perhaps this new funneling of the problem will give you some different insight into a possible solution. I'll scour the web and documentation with the free time I may or may not get today and see what I can come up with, as well. Don't give up hope! :) This sort of data transformation is not an uncommon occurrence, and someone somewhere must have tried to tackle a similar problem--with positive results. *crosses fingers* If not, maybe someone else's idea for a solution can plant the seed. Todd Boyd Web Programmer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
