How to convert Varchar to Double in sql?

mysql sql numeric varchar
Jay Marz picture Jay Marz · Jan 24, 2013 · Viewed 150k times · Source

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

John Woo picture John Woo · Jan 24, 2013

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

Related questions

MySQL - Select only numeric values from varchar column

Consider the following table : create table mixedvalues (value varchar(50)); insert into mixedvalues values ('100'), ('ABC100'), ('200'), ('ABC200'), ('300'), ('ABC300'), ('400'), ('ABC400'), ('500'), ('ABC500'); How can I write a select statement …

Read More
How do I import an SQL file using the command line in MySQL?

I have a .sql file with an export from phpMyAdmin. I want to import it into a different server using the command line. I have a Windows Server 2008 R2 installation. I placed the .sql file on the C drive, and …

Read More
How can I prevent SQL injection in PHP?

If user input is inserted without modification into an SQL query, then the application becomes vulnerable to SQL injection, like in the following example: $unsafe_variable = $_POST['user_input']; mysql_query("INSERT INTO `table` (`column`) VALUES ('$unsafe_variable')"); That's …

Read More