How to convert Varchar to Double in sql?

mysql, numeric, sql, varchar

Solution

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

Problem

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: 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?

Original source