CODEWITHSUNDEEP
sqlsql-servert-sql
Elbelcho
Elbelcho

Reputation: 459

Find non-integer values in a float field

I'm having trouble finding a way to write a query that will return all non-integers in a float column in SQL Server 2005/8.

I have a float field where the majority of the data in it is actually integers, but I'd like to take a look at the rows where the values actually contain a decimal value. The first thing I tried was modulus 1, but the % operator doesn't work on float values.

Thanks for your help!

Upvotes: 4

Views: 9052

Answers (4)

Eelvex
Eelvex

Reputation: 9133

You may also try this:

SELECT * FROM tbl WHERE col != ROUND(col)

Upvotes: 3

Crozin
Crozin

Reputation: 44376

I don't know exact syntax of MSSQL, however you could try something like that (pseudo-code)

SELECT ... FROM tbl_name WHERE col_name != CAST(col_name AS INTEGER)

Upvotes: 7

DForck42
DForck42

Reputation: 20357

are you just wanting the rows with a decimal in it?

select field
from table
where field like '%.%'

Upvotes: 6

Paddy
Paddy

Reputation: 33867

Try something like:

SELECT * 
FROM MyTable
WHERE (CONVERT(INT, floatField) - floatField) <> 0

Upvotes: 4

Related Questions