Jay Marz Jay Marz - 9 months ago 54
SQL Question

How to convert Varchar to Double in sql?

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
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
is not correct. So I decided to convert the varchar to numeric so that it might be sorted perfectly. Any idea?

Answer Source

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

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