SQL ORDER chars numerically

sql char type-conversion sql-order-by numerical
T.T.T. picture T.T.T. · Jan 29, 2009 · Viewed 81.2k times · Source

I have a column of numbers stored as chars. When I do a ORDER BY for this column I get the following:

100
131
200
21
30
31000
etc.

How can I order these chars numerically? Do I need to convert something or is there already an SQL command or function for this?

Thank You.

Answer

Ray Hidayat picture Ray Hidayat · Jan 29, 2009

Try this:

ORDER BY CAST(thecolumn AS int)

Related questions

SQL query for a carriage return in a string and ultimately removing carriage return

SQL query for a carriage return in a string and ultimately removing carriage return I have some data in a table and there are some carriage returns in places where I don't want them. I am trying to write a …

Read More
What is the major difference between Varchar2 and char

Creating Table: CREATE TABLE test ( charcol CHAR(10), varcharcol VARCHAR2(10)); SELECT LENGTH(charcol), LENGTH(varcharcol) FROM test; Result: LENGTH(CHARCOL) LENGTH(VARCHARCOL) --------------- ------------------ 10 1 Please Let me know what is the difference between Varchar2 and char? At what times we use …

Read More
How to count the number of times a character appears in a SQL column?

For a user logging table I have in a SQL database, I track the some of the parameters off of a report request. The report allows multiple ID's to be passed to it and I store all of those in …

Read More