I have a DB that has a numeric field and I need to search all the rows and return only the non whole numbers.
I have tried the query below and it keeps retuning records that have 0.
SELECT
li.QTY
FROM
TABLE LI
WHERE
li.QTY like '%.%'
You can use LIKE only with char fields, not with number (integer or float) ones.
If by "whole numbers" you mean 0.0 , 2.0 , -5.0 , etc. and not 12.5 , 0.67 then this can do:
SELECT li.QTY
FROM TABLE LI
WHERE li.QTY != ROUND(li.QTY , 0)
;
(for SQL-Server: edited the TRUNC into ROUND) You could also use the FLOOR or CEILING functions:
SELECT li.QTY
FROM TABLE LI
WHERE li.QTY != FLOOR(li.QTY)
;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With