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:

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

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.

Did you like this post?

	subscribe via rss		subscribe via e-mail
	print this post		follow me on twitter

Feedback: