CODEWITHSUNDEEP
mysql
Zaid Kajee
Zaid Kajee

Reputation: 712

Mysql Order By where alpha numeric

Hi have a result which returns as follows 

ORDER BY ProductCode ASC    

CU522-15
CU522-17
CU522-18
CU522-19
CU522-4

I am trying to get cu522-4 to be first and not last . I can't change the ProductCode as it needs to match another system. Any Ideas ?

Upvotes: 1

Views: 53

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

In the event that your product code prefix is not always the same length, you can do:

order by substring_index(ProductCode, '-', 1),
         substring_index(ProductCode, '-', -1) + 0;

The + 0 does a numeric conversion of the value, which is what you want for sorting.

Upvotes: 0

cornelb
cornelb

Reputation: 6066

Try with 

SELECT * FROM `mytable` ORDER BY LENGTH(ProductCode), ProductCode

CU522-4 will be first because it has less characters

The others will be sorted normally

Upvotes: 2

amit
amit

Reputation: 874

Going on the assumption it's always '-' this should work:

    SELECT   *
FROM     table
ORDER BY CAST(SUBSTRING(ProductCode,LOCATE('-',ProductCode)+1) AS SIGNED)

Use POSITION to find the '-', SUBSTRING to grab the number after it, and CAST to make it a comparable value.

Upvotes: 3

Related Questions