How to convert Varchar to Double in sql?

Question

I have a problem with my query when I tried to convert the varchar field to double (numeric). I have this sql expression:

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 this field is in varchar, the result is sometimes erroneous. This is the result when I tried to execute a query using this statement:

 SELECT fullName, totalBal FROM client_info ORDER BY totalBal DESC 

Result set:

The sorting of totalBal is incorrect. So I decided to convert varchar to numeric so that it can be sorted perfectly. Any idea?

Answer 1

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

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

Answer 2

This may be more desirable, i.e. use float instead

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