CODEWITHSUNDEEP
sqlsorting
davorn
davorn

Reputation: 433

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?

Upvotes: 3

Views: 11076

Answers (4)

SFA
SFA

Reputation:

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

Upvotes: 0

Aaron Digulla
Aaron Digulla

Reputation: 328830

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.

Upvotes: 0

John Sansom
John Sansom

Reputation: 41899

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?

Upvotes: 1

tehvan
tehvan

Reputation: 10369

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

Upvotes: 1

Related Questions