3

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]%';

Charles Bunn
  • 70
  • 1
  • 1
  • 9
  • Are you asking for a query that returns rows containing a digit one through nine in this field, or a query that returns all rows with a column containing 0 or 1 if something is true, or ... – dcaswell Aug 24 '13 at 16:25
  • For now yes but depending on what I find I may increase the value from zero to say something like 100 or 1000. So just looking for the occurrence of 1-9 as a test for being larger than zero won't work. – Charles Bunn Aug 24 '13 at 16:30

1 Answers1

9

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

Fabien TheSolution
  • 5,055
  • 1
  • 18
  • 30