March 02, 2011

SQL Server: Search Non Round Numbers

Here’s a simple query that searches all non round numbers from a table. So the following are non-round numbers 20.00, 24.0 and the following are round numbers 20.20, 24.42 and so on.

Here’s the query:

sql nonround numbers

Here’s the same query for you to try out:

DECLARE @TT TABLE (
id int,
salary float
);

INSERT INTO @TT VALUES (1, 23.44);
INSERT INTO @TT VALUES (2, 21.00);
INSERT INTO @TT VALUES (3, 20.00);
INSERT INTO @TT VALUES (4, 53.30);
INSERT INTO @TT VALUES (5, 11.00);

SELECT * FROM @TT
WHERE
ROUND(salary, 10) - FLOOR(ROUND(salary, 10)) = 0

OUTPUT

image

Note: Alternatively, you can also write the same query using WHERE (salary – floor(salary)) = 0, or WHERE salary != FLOOR(salary), but I use the query shown above (learnt this tip from Erland Sommarskog) which can also handle values like 20.00000000000097. If you are using the Money datatype, then the alternatives I suggested will work without an issue, since it is a fixed point type.


About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal

2 comments:

Madhivanan said...

Suprotim,

Another method is



SELECT * FROM @TT
WHERE
salary=cast(salary as int)

Suprotim Agarwal said...

Smart solution Madhivanan!