Order By alphanumeric values like numeric

Question

there is a table's fields on MSSQL Serrver 2005 as VARCHAR. It contains alphanumeric values like "A,B,C,D ... 1,2,3,...,10,11,12" etc.

When i use below codes;

....   
ORDER BY TableFiledName

Ordering result is as follow 11,12,1,2,3 etc.

When i use codes as below,

....   
 ORDER BY
    CASE WHEN ISNUMERIC(TableFiledName) = 0 THEN CAST(TableFiledNameAS INT) ELSE TableFiledName END

I get error message as below;

Msg 8114, Level 16, State 5, Line 1 Error converting data type varchar to float.

How can get like this ordering result: 1,2,3,4,5,6,7,8,9,10,11,12 etc..

Thanks in advance.

Try this approach: http://stackoverflow.com/questions/34509/natural-human-alpha-numeric-sort-in-microsoft-sql-2005 — Per Hoffmann Olsen, Nov 05 '12 at 11:45

podiluska · Accepted Answer · 2012-11-05T10:54:37.183

ISNUMERIC returns 1 when the field is numeric.

So your first problem is that it should be ...

   CASE WHEN ISNUMERIC(TableFiledName) = 1 THEN

But this alone won't work.

You need to prefix the values with zeroes and take the rightmost

 order by 
      case when ISNUMERIC(FieldName) =1 
      then right('000000000'+FieldName, 5)
      else FieldName 
      end

Using 5 allows for numbers up to 99999 - if your numbers are higher, increase that number.

This will put the numbers before the letters. If you want the letters before the numbers, then you can add an isnumeric to the sort order - ie:

 order by
      isnumeric(FieldName),
      case...

This won't cope with decimals, but you haven't mentioned them

1 Answers1