1

hi there i have a mysql database populated with products, each product has a product code the product code contains letters and numbers eg. TL541 , TL75 etc

my client wants the products ordered by code, but TL541 comes before TL75 presumably because 5 is lower than 7

any ideas how to order this in numerical order?

Ira Baxter
  • 93,541
  • 22
  • 172
  • 341
Dizzy Bryan High
  • 2,015
  • 9
  • 38
  • 61
  • 1
    The question of [Natural Sort in MySQL](http://stackoverflow.com/questions/153633/natural-sort-in-mysql) comes up from time to time: in short: it's probably not possibly unless a very limited subset, where you can cast the value to an integer (in this case: reverse string, cast as integer, reverse string, cast as integer). – Wrikken Nov 04 '10 at 22:49
  • 1
    On a side note: as you indicated PHP in your question: there the function would be [`natsort`](http://www.php.net/natsort) – Wrikken Nov 04 '10 at 22:58
  • think the best way to do things is to get the data, perform natsort in php and resave to the db saving the new order to the order column (how i had originally set up the ordering, thanks for the info, had been on google for ages not getting anywhere – Dizzy Bryan High Nov 04 '10 at 23:16
  • hmmm natsort keeps the array id so wont be able to resave the array order to the db, would have to natsort then add to a new array with a for each, would that go through in numerical order of the array id or would it add them to the new array in the natsort order? – Dizzy Bryan High Nov 04 '10 at 23:30

2 Answers2

0

That's a problem with having a database that isn't normalized. If you want to treat the prefix and the number as separate data elements, then they need to be put in their own columns.

JOTN
  • 6,120
  • 2
  • 26
  • 31
  • yeah, tell me about it, i originally created the products table with an order column, i do with most tables i create, but the client wants to order by product code now, theres about 3000 records in the db so could rearrange the order but that would be a massive task me thinks – Dizzy Bryan High Nov 04 '10 at 23:11
0

For the very limited examples, a kludge with bad performance:

ORDER BY 
   REPLACE(code,REVERSE(CAST(REVERSE(code) AS UNSIGNED)),''),
   CAST(REVERSE(CAST(REVERSE(code) AS UNSIGNED)) AS UNSIGNED)
Wrikken
  • 69,272
  • 8
  • 97
  • 136