SQL Number field search for integers

Question

I have a DB that has a numeric field, and I need to search all rows and return only non-integer numbers.

I tried to execute the query below and it continues to reconfigure records containing 0.

SELECT li.QTY FROM TABLE LI WHERE li.QTY like '%.%' 

Answer 1

You can use LIKE only with char fields, and not with numbers (integer or floating). A.

If by "integers" you mean 0,0, 2,0, -5,0, etc., and not 12.5, 0.67, then this can be done:

 SELECT li.QTY FROM TABLE LI WHERE li.QTY != ROUND(li.QTY , 0) ; 

(for SQL-Server: edited TRUNC in ROUND) You can also use the FLOOR or CEILING functions:

 SELECT li.QTY FROM TABLE LI WHERE li.QTY != FLOOR(li.QTY) ; 

Answer 2

Why it does not work. When QTY is a numeric column, when you show it or implicitly convert it to varchar (LIKE does it implicitly), ALL numbers will be converted to the same number of decimal places.

Consider this SQL statement

 with TBL(qty) as (select 1.1 union all select 3) SELECT li.QTY FROM TBL LI WHERE li.QTY like '%.%' 

Exit

 1.1 3.0 << this contains "." even if it does not need to 

Copy it to bigint and it will drop any decimal numbers and then compare it again.

 SELECT li.QTY FROM TBL LI WHERE li.QTY <> CAST(qty as bigint) 

---

If you MUST use LIKE (or just for the show ..)

 SELECT li.QTY, CONVERT(varchar, li.qty) FROM TBL LI WHERE li.QTY LIKE '%.%[^0]%' 

Answer 3

 --Most Def...Cast as an int...Save you from validation of anything. Especially for conversions of dates into a whole date since Microsoft does not like a round down date function. --For example: --For every 300 hours an employee works they receive 1 whole day of vacation --partial days of vacation will not be counted Declare @SumHours as decimal(38,10), @VacationValidation as int Set @SumHours = 3121.30000000000000000000 Set @VacationValidation = 300 Select cast(((@SumHours)/(@VacationValidation)) as int) Select day(((@SumHours)/(@VacationValidation))) -1 --without casting as an int, I would need to validated the Day function to make sure that I'm not --offsetting a whole day by one day. 

Answer 4

The CAST function works for me.

 SELECT li.QTY FROM TABLE LI WHERE li.QTY != CAST(li.QTY AS INTEGER)