'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/4and 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/4Is there a way to do this?
-
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 ...
0 comments:
Post a Comment