On Thursday, April 25, 2002, at 04:40 PM, Steve Buehler wrote:
> Is there a way to use "ORDER BY", or something else, that will order > the following in numerical order instead of alphabetical order? The > column that the data is in is: > short_description varchar(20) default NULL. > > 21UG3 > 10UB5 > 100UB6 > 1UG1 What you want to do is somewhat difficult because of the inconsistency -- in the fourth example, the letter comes in the second position and in the others the letter is in the third or fourth position. So you can't reliably use the MySQL SUBSTRING() function to get just the first two digits, or what have you. But you could pad the left with zeroes using LPAD(), use SUBSTRING() to get the number-only part, and then add zero to convert it to a number. SELECT * FROM test ORDER BY SUBSTRING(LPAD(short_description, 7, '0'), 1, 4) + 0 The only thing is that if your data is of greater length than the ones you've provided, you'll have to LPAD it to a greater number (change the 7 to something larger than the longest string) and whatever number you increment this by, also increment the LEN parameter to the SUBSTRING() function. So you could also write the above as SELECT * FROM test ORDER BY SUBSTRING(LPAD(short_description, 11, '0'), 1, 8) + 0 and it should give you the exact same effect. > I use PHP to access the mysql database so if MySQL can't do it, does > anybody know of a way to do it in combination with PHP? If you decide to go this route, and I wouldn't since MySQL can do the work for you, make an array and put each record into an element of that array. PHP has some very powerful array-sorting functions, check them out at php.net to see the best way to sort your data. Most likely you'd do a similar thing as above in PHP, so why not just save the extra processing cycles by doing it in the database? Erik ---- Erik Price Web Developer Temp Media Lab, H.H. Brown [EMAIL PROTECTED] --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
