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)"
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)
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