This week's giveaway is in the Android forum.
We're giving away four copies of Android Security Essentials Live Lessons and have Godfrey Nolan on-line!
See this thread for details.
The moose likes JDBC and the fly likes Order by with Varchar datatype Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Order by with Varchar datatype" Watch "Order by with Varchar datatype" New topic
Author

Order by with Varchar datatype

mark hunt
Greenhorn

Joined: May 02, 2007
Posts: 8
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:

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.


Thanks for your time.

Mark
[ June 22, 2007: Message edited by: mark hunt ]
Chad Clites
Ranch Hand

Joined: Aug 16, 2005
Posts: 134
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
Bartender

Joined: Dec 20, 2006
Posts: 2491
    
    8

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


OCUP UML fundamental and ITIL foundation
youtube channel
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Order by with Varchar datatype
 
Similar Threads
How to insert a data of datetype timestamp into Sqlserver2000
Oracle JDBC Thin Client behaviour concerning date data types and it's format.
problem with sql query to_date
Create table error
publishing to a registry