Sorting Numbers Stored As String

UDA
Fan

Sorting Numbers Stored As String

Hi,

I have a field named 'Page_Spread' which is an Integer data type. I am casting it to a CHAR(4), but upon sorting, it is not sorting in the correct way. EG 1,2,11 IS being sorted 1,11,2. The reason is obviously because I am converting to a string which I need to do for other reasons, but does anyone know how to convert to string & stll have it sorted in the correct manner??
1 REPLY
Enthusiast

Re: Sorting Numbers Stored As String

The cast expression can be given a different name. Then you can reference the original column name in the "order by", e.g.:

select cast(b as char(4)) as b1
from t1
order by b;

*** Query completed. 2 rows found. One column returned.
*** Total elapsed time was 1 second.

b1
----
2
11