Sorting through Query

• Tonmoy Roy

  Newbie

  Points: 1

  More actions

  November 11, 2005 at 4:22 am

  #89896

  Hi,

  I have a table which contains a field "Name". The datatype of the field is varchar. The dummy values in the field are e.g. 1,10,2,15,abc,12,20.

  If I use just "Order By Name", it gives me 1,10,12,15,2,20,abc. But, I want the output as  : 1,2,10,12,15,20,abc.

  How can I solve this without writing any stored procedure or any other type of coding. I want to use just Query(SQL Statement).

   

  Please Reply at earliest.

   

  Thanks,

  Tonmoy Roy

   

• val_cc

  SSC Rookie

  Points: 37

  More actions

  November 11, 2005 at 4:52 am

  #603390

  Try this query. 

  SELECT  *,

     Tmp1 = Case WHEN Isnumeric(Name) = 1 THEN CAST(Name AS Numeric) ELSE 99999 END,

     Tmp2 = Case WHEN Isnumeric(Name) = 0 THEN Name ELSE '' END

  FROM   [TableName]

  ORDER BY Tmp1, Tmp2

  Note:  the digits of 99999 in Tmp1 is depend on the width of your "Name" field.