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.


Did you like this post?
kick it on DotNetKicks.com
subscribe via rss subscribe via e-mail
print this post follow me on twitter


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

 
  Feedback:

comments

2 Responses to "SQL Server: Search Non Round Numbers"
  1. Madhivanan said...
    March 4, 2011 at 3:33 AM

    Suprotim,

    Another method is



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

  2. Suprotim Agarwal said...
    March 6, 2011 at 9:04 PM

    Smart solution Madhivanan!

 

Copyright © 2009-2014 All Rights Reserved for SQLServerCurry.com by Suprotim Agarwal | Terms and Conditions