Sorting numeric value in a SQL VARCHAR column

We had some sorting issues with records coming back from our database. Nothing major, just something like this:

10, 100, 110, 120, 20, 30, 40, 50, 60, 70, 80, 90

That just ain’t right. What we wanted, of course, was this:

10, 20, 30, 40, 50, 60, 70, 80, 90, 100, 110, 120

No problem, just add an ORDER BY clause and everything was fine. The only minor issue was that the column we were sorting was a VARCHAR, so adding a simple sort did nothing because as far as SQL Server was concerned, the first list was the correct way to sort a VARCHAR column. In order to override SQL Server’s method of sorting, I had to first cast the line number as an INT using the CAST function, and all was well:

     ORDER BY CAST(LineNumber AS INT) ASC
Advertisements
  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: