How to sort varchar column (SQL) that contains number, chars, characters?

'Order by' returns this result below

05 05/1-1 05/1-2 05/1-3 05/1-4 05/1-5 05/1-6 05/1-7 05/1 05/2-1 05/2-2 05/2-3 05/2-4 05/2 05/3 05/4

and this order below is OK

05 05/1 05/1-1 05/1-2 05/1-3 05/1-4 05/1-5 05/1-6 05/1-7 05/2 05/2-1 05/2-2 05/2-3 05/2-4 05/3 05/4

Is there a way to do this?

From stackoverflow

• If possible, try to split up the data, so that any numeric information is in its own field. String data and numeric data together in a field will always result in string type of data, so that 'A2' > 'A11'.

• Hi,

  You need to cast/convert the varchar data to a numeric data type and then perform an order by sort on the data.

  You will likely need to split your data string also, so example order by caluse might be:

  order by 
  convert(int,left(columnName,2)) asc, 
  convert(int,subtring(columnName,4`,2))
  

  This will depend on which string elements represent which date components.

  Make sense?

• Alter the table and add a compare column. Write a small program which reads the strings and converts them into a format which the database can convert. In your case, a DATE is a good candidate, I guess.

  In the general case, use a VARCHAR column and format all numbers to five (or more) digits (with leading zeroes/spaces, i.e. right aligned).

  After that, you can use the compare column to order the data.

• If I were you I would order by a tricky expression. Let's assume that before a slash you have at most 2 or 3 digits. If you write

  order by case charindex('/', val)
             when 0 then convert(int, val)
             else convert(int, substr(val, 1, charindex('/', val) -1)
           end * 1000
             + case charindex('/', val)
                 when 0 then 0
                 else convert(float, replace(substring(val, 1 + charindex('/', val),
                                                       length(val)), '-', '.'))
               end
  

  If I'm not mistyped anything, the following should convert 05 to 5000, 05/1 to 5001, 05/1-1 to 5001.1, and things should sort the way you want, assuming you always have a single digit at most after the hyphen. Otherwise you can probably work around it by splitting and left-padding with the suitable number of zeroes, but the expression would get much uglier ...