convert varchar(8) values and use ASC in T-SQL

I have a data in the table in the form below and it is in varchar(8) datatype

Total

100
101
104.5
88
1038
64
108.3
10872
900

I like to use ASC in T-sql so that I can display it into ascending order, however I cannot d开发者_运维问答o it as it is in varchar(8) form

For example

select Total from
Table A
Order by Total ASC

How to first add these values into Temporary temp table? and How to convert this varchar(8) values and into what? so that you can display them in ASC or ascending order using T-SQL query? Anyone?

You could cast the value like this.

SELECT 
    Total 
FROM Table A
ORDER BY CAST(Total AS FLOAT) ASC

You may lose data converting back to float.

So here is a varchar based sort.

DECLARE @badDesign TABLE (floatcol varchar(8) NOT NULL);
INSERT @badDesign VALUES ('100'),('101'),('104.5'),('88'),('1038'),('64'),('108.3'),('10872'),('900'),('108'), ('108.32'), ('108.4')

SELECT *
FROM @badDesign
ORDER BY
   RIGHT('00000000' +
       CASE
           WHEN CHARINDEX('.', floatcol) = 0 THEN floatcol
           ELSE LEFT(floatcol, CHARINDEX('.', floatcol)-1)
       END
       , 8),
   CASE
       WHEN CHARINDEX('.', floatcol) = 0 THEN '.0'
       ELSE SUBSTRING(floatcol, CHARINDEX('.', floatcol)+1, 8)
   END

The values from your example looks like float numbers. So

1) Since they all have no more than 8 digits, you can cast it to float(53) (it has about 15 decimal digits precision) without loss of data. Or to decimal(15,7) to be completely sure.

2) Generally it's strange to store float values as strings in the database.

Use CAST or CONVERT functions, e.g.:

select Total from
Table A
Order by CAST(Total as float) ASC

Reference: http://msdn.microsoft.com/en-us/library/ms187928.aspx

Unless you want to keep the converted values, you don't need to store it in a temporary table. Just convert them for the sorting:

select Total
from [Table A]
order by cast(Total as float)

(Ascending is the default way to sort, so you don't have to specify that.)