Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Search a Numeric Field for non whole numbers

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 '%.%'
like image 695
djshortbus Avatar asked Feb 07 '11 22:02

djshortbus


1 Answers

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)
;
like image 200
ypercubeᵀᴹ Avatar answered Nov 03 '22 10:11

ypercubeᵀᴹ