SQL Query - How do I convert a text field containing dollar sign to decimal only (strip out dollar sign)

mysql null char decimal dollar-sign
Charles Bunn picture Charles Bunn · Aug 24, 2013 · Viewed 11.3k times · Source

I am running a query on a MySQl database and I have a field of text characters that contains a dollar amount with a dollar sign and a number (ie $1,345.89) I need to do a check for not NULL (some of the text fields are NULL) and that the value is greater than 0. How can I convert this text field to a decimal value so that I can verify that it is greater than 0. I do not want to alter the original text in the database.

Thanks

Here is a query I tried but it does not work:

SELECT parcel, year, due FROMdeliquent_property WHERE year IN ('2013', '2012', '2011', '2010') GROUP BY parcel HAVING due LIKE '%[0-9]%';

Answer

Fabien TheSolution picture Fabien TheSolution · Aug 24, 2013

This query will do the trick :

SELECT ID, 
       CAST(REPLACE(REPLACE(IFNULL(Amount,0),',',''),'$','') AS DECIMAL(10,2)) 
FROM Table1
WHERE CAST(REPLACE(REPLACE(IFNULL(Amount,0),',',''),'$','') AS DECIMAL(10,2)) > 0

See SQLFIDDLE : http://www.sqlfiddle.com/#!2/55f433/1/0

Related questions

MySQL: selecting rows where a column is null

I'm having a problem where when I try to select the rows that have a NULL for a certain column, it returns an empty set. However, when I look at the table in phpMyAdmin, it says null for most of …

Read More
MySQL and PHP - insert NULL rather than empty string

I have a MySQL statement that inserts some variables into the database. I recently added 2 fields which are optional ($intLat, $intLng). Right now, if these values are not entered I pass along an empty string as a value. How do …

Read More
Checking for an empty field with MySQL

I've wrote a query to check for users with certain criteria, one being they have an email address. Our site will allow a user to have or not have an email address. $aUsers=$this->readToArray(' SELECT `userID` FROM `…

Read More