Order by with Varchar datatype

[mark hunt]

Hi,
I'm using Sybase. I have a table A that contains VendorNum, InvoiceNum as varchar datatype. If i do a select on the table and order it by VendorNum, InvoiceNum, it's not giving me expected results. It's returning the followin:

 VendorNum      InvoiceNum
       181909             12
       181909             14
       181909             2
       181909             3

But if i convert it in int , it gives me expected results. meaning invoiceNUm 2, 3, 12, 14. But i can't convert it in int because the data might convert alphbets, - etc. Could someone please shed some light on doing order by with varchar datatype and getting expected results. This is what i was expecting to recieve.

 VendorNum      InvoiceNum
       181909             2
       181909             3
       181909             12
       181909             14

Thanks for your time.

Mark
[ June 22, 2007: Message edited by: mark hunt ]

[Chad Clites]

You are getting exactly the results I would expect when ordering by varchar. One trick that might work is (for example)
SELECT VendorNum, InvoiceNum FROM someTable ORDER BY VendorNum, HEX(InvoiceNum);

I don't know if Sybase supports it, or if it works with multiple conditions, but worth a shot.

[Jan Cumps]

Leftpadding with blanks also works to get propper sorting. Say that your maximum number of digits is 10,
than you can order by lpad(yourfield, 10)

Regards, Jan