Mysql Select On Varchar Treat As Number

wmuckell · 11th June 2008, 12:57 PM

I have a database table with PRICE as a varchar. However I want to do a search for those value in the table that are < 1000 for example. And this will disclude all the other STRING values, lik POA etc.

Any ideas?

APC-Andy · 11th June 2008, 02:53 PM

Look into the MySQL functions Cast and Convert

MySQL :: MySQL 5.0 Reference Manual :: 9.1.7.2 CONVERT() and CAST()

Depending on how often you will be querying it may be more efficient to store the numeric values in a separate column instead of using the above method.

HTH

Andrew

wmuckell · 11th June 2008, 03:37 PM

Quote:

Originally Posted by APC-Andy

Look into the MySQL functions Cast and Convert

MySQL :: MySQL 5.0 Reference Manual :: 9.1.7.2 CONVERT() and CAST()

Depending on how often you will be querying it may be more efficient to store the numeric values in a separate column instead of using the above method.

HTH

Andrew

Tried both

WHERE CONVERT(ad_price, DECIMAL) < 1000

and

WHERE CAST(ad_price as DECIMAL) < 1000

and neither worked??