Jay Marz Jay Marz - 16 days ago 5
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
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?

Answer

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
Comments