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

Question

'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?

Answer 1

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'.

Answer 2

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?

Answer 3

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.

Answer 4

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 ...