Sorting varchar data numerically

[yashshah]

Hey guys i have this problem here,

I have some data which goes like

1900

1899

1898

 

100

C-1

C-21

C-100

A-200

A-100

etc..

 

These are basically item codes which needs to be sorted numerically. This column rite now is of varchar type.

The data what i am looking for is something like

 

1900

1899

1898

A-200

A-100

C-100

C-21

C-1

 

This is killing my project here and my dean wont accept the project without this..Need help badly.

Thanks in advance

[petroz]

Post your select query.

[yashshah]

Well this is how my select query looks for now..

SELECT * FROM item_master WHERE style="'.$style.'" AND catcode="'.$catcode.'" ORDER BY code DESC LIMIT '.$start.','.$per_page;

[petroz]

You can do...

 

ORDER BY style,code DESC LIMIT '.$start.','.$per_page;

or

ORDER BY code,style DESC LIMIT '.$start.','.$per_page;

 

let me know if that helps.

 

[yashshah]

Well not really.. I need them sorted according to the item codes.

The codes go as.

 

2000 to say 100.

A few have C-200 to C-01

A few are U-200 to U-01

Others would be BU-200 to U-01.

 

What is desirable is that the numbers get sorted desc first. Followed with an alphabet sorting along with a numerical sort approach.

 

Let me know if you dont understand what im saying here.

[petroz]

Sorry, I am not following you. Is there both numerical and alphabetical data in one column your trying to sort?

[yashshah]

Yup ur rite.. these are item codes. A few of them are numerical and a few are prefixed by Alphabets followed by a hyphen and a numerical value i.e A-1 or B-1..

The criteria is to get the numerical ones first and then the ones which prefixes an alphabet.

[petroz]

I cant find anything on sorting like that... Do you have another column you could sort by?

[yashshah]

Umm i dont think so.. hey can we simple get the numerical onces come first and sorted desc leaving the ones with the alphabets the way it is?

[PFMaBiSmAd]

You would need to use SUBSTRING_INDEX() to get the parts before and/or after the -. You would then order by those parts.

 

Ref: http://dev.mysql.com/doc/refman/5.1/en/string-functions.html#function_substring-index

[fenway]

You'll have to sort the field into two parts -- not efficient at all -- but possible.

 

EDIT: just missed the post post by 2 seconds.