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

Question

'Sort 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 is lower than 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 any way to do this?

Answer 1

If possible, try splitting the data so that any numerical information is in its own field. String data and numeric data together in a field will always contain a string data type, so "A2"> "A11".

Answer 2

varchar , .

, order by caluse :

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

, , .

?

Answer 3

. , , . , DATE - .

VARCHAR ( ) ( /, ).

.

Answer 4

, . , 2 3 .

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 mistaken, the following should convert from 05 to 5000, from 05/1 to 5001, 05 / 1-1 to 5001.1, and everything should sort as you want, provided that you always have one digit in most after a hyphen. Otherwise, you can probably get around this by splitting and left padding with a suitable number of zeros, but the expression will be much uglier ...