Selecting a Float in MySQL

Selecting a float in MySQL

Casting to a decimal worked for me:

SELECT * FROM table WHERE CAST(price AS DECIMAL) = CAST(101.31 AS DECIMAL);

However, you may want to consider just making the price column a DECIMAL in the first place. DECIMAL is generally considered to be the best type to use when dealing with monetary values.

SELECT a FLOAT with given precision

Yes, just specify the second parameter for ROUND()

SELECT ROUND(23.298, <precision>);

With this, you can specify the number of digits you would like to get returned. (you can even have negative values, in wither case, the input gets rounded to 10^(-precision)).

Or you could use CAST as DECIMAL:

 CAST(2.5 AS DECIMAL(20,2))

(Note: this latter would work with textual inputs too, like CAST('2.5' AS DECIMAL(20,2)), but in this case it is about FLOAT inputs)

mysql float data not selecting in where clause

Use decimal instead of float.

A decimal(10,2) will have 2 and only 2 decimal places and can be compared in the same manner as integers.
Especially for monetairy values you should always use decimal, but anywhere where rounding errors are unwanted, decimal is a good choice.

See: http://dev.mysql.com/doc/refman/5.1/en/precision-math-decimal-changes.html

Or
MySQL DECIMAL Data Type Characteristics

Selecting a float in MySQL

Casting to a decimal worked for me:

SELECT * FROM table WHERE CAST(price AS DECIMAL) = CAST(101.31 AS DECIMAL);

However, you may want to consider just making the price column a DECIMAL in the first place. DECIMAL is generally considered to be the best type to use when dealing with monetary values.

selecting decimal float in mysql query

The correct way to do floating-point number comparison is to first decide on an acceptable tolerance for differences between the numbers and then do the comparison against the tolerance value. For example, if we agree that floating-point numbers should be regarded the same if they are same within a precision of one in ten thousand (0.0001), the comparison should be written to find differences larger than the tolerance value:

   ABS(t6.field_number - 2.60) <= 0.0001

Check for equality on a MySQL Float field

Usually with these types of questions it's good to provide a small example to replicate your results.

Usually testing for exact float values is a bad idea since floating point precision isn't an exact science. It's much better to use some tolerance.

create table foo1 (col1 float);

insert into foo1 values (2.18);
select * from foo1 where abs(col1-2.18) <= 1e-6

MySQL function to select float value as integer if its decimal point is 0

I tried using

SELECT GROUP_CONCAT(CONCAT(reason_code, ':', COALESCE(identifier, ''), '*',(TRIM(TRAILING '.' FROM(TRIM(TRAILING '0' FROM amount)))) * -1)) AS accout_groups FROM adjustments WHERE check_id = 1 GROUP BY department_id

This is working

---

Related Topics