How to convert Varchar to Double in sql?

Question

I have problem with my query when I was trying to convert the varchar field to double (numeric). I have this sql statement:

SELECT fullName, CAST(totalBal as numeric(9,2) FROM client_info ORDER BY totalBal DESC

Actually I want to display the values of totalBal in descending order. But since that field is in varchar, the resultset is sometimes wrong. This is the resultset when I tried to query using this statement:

SELECT fullName, totalBal FROM client_info ORDER BY totalBal DESC

Resultset is:

enter image description here

The sorting of totalBal is not correct. So I decided to convert the varchar to numeric so that it might be sorted perfectly. Any idea?

score 47 · Accepted Answer · answered Jan 24 '13 at 07:36

47

use DECIMAL() or NUMERIC() as they are fixed precision and scale numbers.

SELECT fullName, 
       CAST(totalBal as DECIMAL(9,2)) _totalBal
FROM client_info 
ORDER BY _totalBal DESC

answered Jan 24 '13 at 07:36

John Woo

258,903
69
498
492

1

Thank you for this idea... updated the code with this... `SELECT fullName, CAST( totalBal AS DECIMAL( 9, 2 ) ) AS totBal FROM client_info ORDER BY totBal DESC` – Jay Marz Jan 24 '13 at 07:47

score 3 · Answer 2 · answered Nov 18 '16 at 10:12

3

This might be more desirable, that is use float instead

SELECT fullName, CAST(totalBal as float) totalBal FROM client_info ORDER BY totalBal DESC

answered Nov 18 '16 at 10:12

TheTechGuy

16,560
16
115
136

5

"CAST as float" is not valid in MySQL. So this doesn't work at all. – m4ttsson Aug 14 '17 at 10:05
2

The question is about SQL, not about MySQL and CAST as float is valid SQL. – Jose Florido Dec 01 '18 at 05:56
The first tag in question is `mysql` so it was perhaps my bad I answered `MSSQL` version. – TheTechGuy Dec 04 '18 at 06:36

How to convert Varchar to Double in sql?

2 Answers2