ted_chou12 Posted June 10, 2009 Share Posted June 10, 2009 Hi I have a column that is in varchar and I tried to order by asc, and it looks like this L1 L10 L11 L12 L2 L3 L4 L5 L6 L7 ... instead i want it to be numerical logically ordered... eg L1 L2 L3 L4 ... L10 L11 is there a special function that is needed to be added in the order by query for it to be ordered properly? Thanks, Ted Quote Link to comment Share on other sites More sharing options...
gassaz Posted June 10, 2009 Share Posted June 10, 2009 Try this: select ID from s_copy order by SUBSTRING(ID from 2) + 0 Quote Link to comment Share on other sites More sharing options...
ted_chou12 Posted June 10, 2009 Author Share Posted June 10, 2009 Hi, thanks, that kind of worked a bit, but the result came out like this: 1 2 3 4 5 6 7 8 9 10 L1 D1 11 S1 L2 12 S2 L3 13 S3 L4 S4 L5 S5 L6 S6 L7 S7 L8 S8 S9 I guess the numbered are ordered, but the letters are ordered differently :-\, i found out that this is a well known bug in mysql. Thanks for your help though Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 10, 2009 Share Posted June 10, 2009 It's not a bug, it's you expecting strings to act like numbers. You also did not bother to mention in the first post that there were numbers and letter-numbers involved. When there is a leading letter, will there always be only one? The most direct solution is for you to make all the number fields the same format/length. Instead of L1, use L01 (assuming a maximum number of 99 will be used.) Quote Link to comment Share on other sites More sharing options...
ted_chou12 Posted June 10, 2009 Author Share Posted June 10, 2009 http://www.webdeveloper.com/forum/showthread.php?t=101174 This is a known bug in mysql where it orders based on value length as well. Sorry for not mentioning it in my first post, my bad. The field will have either numbers or letters in the field, and not to assume that it will only reach up to 99. thanks. Ted Quote Link to comment Share on other sites More sharing options...
Ken2k7 Posted June 11, 2009 Share Posted June 11, 2009 Don't store them in Letter-Number format. Quote Link to comment Share on other sites More sharing options...
PFMaBiSmAd Posted June 11, 2009 Share Posted June 11, 2009 The link you posted to the webdev... thread is equally wrong. Strings are sorted character, by character, left to right. It has nothing to do with the length and it is not a bug. The suggestion to add 0 to a string that contains a number will cause that string to be converted to a number and would work except your issue is different because you also have letter-numbers. Quote Link to comment Share on other sites More sharing options...
fenway Posted June 11, 2009 Share Posted June 11, 2009 you need to sort by both -- LEFT( yourColumn, 1 ) and the the substring to skip the letter. Quote Link to comment Share on other sites More sharing options...
ted_chou12 Posted June 11, 2009 Author Share Posted June 11, 2009 hi, thanks for all the suggestions, LEFT(id, 1), (SUBSTR(id, ((id REGEXP '[^a-z]') + 1)) + 0) ive got the second part, which basically means if the id has not letters in it, read from first character, if it does, read from second which outputs: L1 S1 1 L2 S2 2 ... L10 S10 10 ... but the first part is not putting it correctly, is it possible to use regex to determine if the string has no letters in it, then add a 0 in the front so it becomes 01, 02, 03...? Thanks, Quote Link to comment Share on other sites More sharing options...
fenway Posted June 15, 2009 Share Posted June 15, 2009 Why would you need that? Use "+0" and then sort as a number. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.