ORDER BY varchar as integer??

[MasterKaos]

How can i sort the following VARCHARs in ascending order as if they were integers?
When i ORDER BY product_code i get this:

1116
1119
1120
113
118

but what i want is this:

113
118
1116
1119
1120

I'm importing a clients database into a web app and unfortunately they've broken one of the golden rules of database design - meaningless identifiers.

So although they are mainly numbers, the product code column is declared as VARCHAR(31), because the codes have a few letters thrown in.

----------------------------------------------------------------------------
The first 90% of the code accounts for the first 90% of the development time. The remaining 10% of the code accounts for the other 90% of the development time.

 

[TonyGroves]

You could use:
[tt]
ORDER BY CAST(fld AS UNSIGNED)
[/tt]

 

[dalchri]

SELECT * 
FROM MyTable
ORDER BY CAST(Code AS SIGNED)

I am uncertain how this function will react to your alphanumeric codes or how the results will sort. If there is a pattern to your alphanumeric codes, you may be able or need to parse the number part out of the code for proper sorting.

 

[MasterKaos]

Cheers for that guys, works great! Most of the codes only have letters on the end so the cast works fine. And the ones that begin with letters have different SELECT statements on them so i can just leave out the cast there.

Thanks for the speedy response!!

----------------------------------------------------------------------------
The first 90% of the code accounts for the first 90% of the development time. The remaining 10% of the code accounts for the other 90% of the development time.