问题描述:

I'd like to order by a varchar field that contains text and numeric data.

The values in the database / and also the order outputted with

"ORDER BY CAST(product AS SIGNED)"

  • Doppel 80x200cm
  • Classic 100x200cm
  • Mega 240x250
  • Classic 85x200cm
  • Profi 60x235cm

The ORDER I'd like to have is an order first by the text than by number:

(Please keep in mind that the productnames could be one of the following: Text only, Text+Number, Text+Number+Text)

  • Classic 85x200cm
  • Classic 100x200cm
  • Doppel 80x200cm
  • Mega 240x250
  • Profi 60x235cm

I also tried the following without a correct result:

"ORDER BY CAST(SUBSTRING(product,LOCATE(' ',product)+1) AS SIGNED) ASC"

"ORDER BY CASE WHEN product REGEXP '^[0-9]+$' THEN product*1 else 999999 END"

Any help would be appreciated!

网友答案:

This solution comes quite close. Off course you have to put the names in an php array first. http://php.net/manual/de/function.natcasesort.php

相关阅读:
Top