SELECT QUESTION

[laic]

I just created a table that has a column with data type char, and import data from an Excel file.
However in the Excel file there is a line number but for numbers over 1000 will be showing as 000, 001, 002, 003 . . . After import to database, in the column it shows all right.
After do a select * from clause, it is showing as
1, 2, 3, . . . 96, 97, 98, 000, 001, 002, 003 . . . 100, 101, 102, 103, 104 . . .
And I have no problem with it, I only need to update the column and add 1 before each of those 00X number to make it a over 1000 number.
My question is this, after I update the table and make all these number to be 1000, 1001, 1002, 1003 ....
And perform another SELECT * FROM, without any 'ORDER BY' clause, it is still showing in the original order,
1, 2, 3, . . . . 96, 97, 98, 99, 1000, 1001, 1002, 1003 . . .100, 101, 102 . . .
I am not sure why this is happening?? Even I did a 'ORDER BY' on the line column, it is still showing that.
Do I have to change the column data type to other type??
Please explain this to me.

Thanks a bunch,
John

 

[SQLSister]

That is because it is character data and not numeric data. Character data is ana alphabetic sort and the thousands would come before the hundreds. If you want a true numeric sort you need to chage the datatype to integer.

Questions about posting. See faq183-874

 

[Snowcrash]

Or in your query you could write

SELECT *
FROM TableA
ORDER BY CAST(StringCol AS Integer)

Although if numbers are all you are going to be storing in this field, then datatype of number it should be.