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

Question

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 1

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