ORDER BY - numeric values in varchar field

[NevG]

HELP!!

Can someone please tell me if its possible to sort numeric fields in a varhcar column according to their number??

ie

values I have are test1, 1,11,2,0

and it orders them as 0,1,11,2,test1

I really need it to order as: 0,1,2,11,test1 ...

is this possible without ripping out SQL Servers insides and altering my data definition (which is not an option)

Thanks guys and gals !!

Nev G

 

[donutman]

Add these two values to your list and show us the result: "1Test" and "11Test".
-Karl

 

[NevG]

1
11
11test
1test
2

 

[zemp]

Have you looked into the CAST function. Use it in your where clause to treat varchar as an int

CAST(<Field> as int)

The only problem I forsee is when you also have alpha characters in the field value. You may need to use some sort of ISNumeric check first.

zemp

 

[NevG]

that would be fine if it was always going to be a integer .... however the field is a varchar for a reason - it will contain both alpha numeric in it

which is y I think this is impossible!

any other thoughts

 

[donutman]

Which is correct (note 2 & 11):

I really need it to order as: 0,1,2,11,test1

OR

1
11
11test
1test
2

-Karl

 

[donutman]

I'll give you something to work with assuming that you want non-numeric stuff to be sorted alphanumerically AFTER the numeric data.
Let's assume that your varchar field is length 10.
Select * from MyTable
Order By case isnumeric(TheField)
when 1 then right('00000000000'+TheField,11)
else right(char(255)+rtrim(TheField),11) end
This places either a 0 or a char(255) as the 1st character. This is necessary in the event that one of the values fills the entire field, BUT begins with a 0-9.
-Karl

 

[donutman]

Oops. I think you need the rtrim(TheField) for both cases.
-Karl