find only integers - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

find only integers

Author

Topic

lappin
Posting Yak Master

182 Posts

Posted - 2011-05-25 : 06:53:18

Hi, is there a way to find values from a decimal column which have no decimal places (only full integer values) in the data?

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 06:59:35

You could check to see if there is a fractional portion as in:


case when columnName%1 = 0 then 'No fractional part' else 'Has Fractional Part' end

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-25 : 07:00:01

Just an idea:
WHERE decimal_column - convert(int,decimal_column) = 0

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-05-25 : 07:02:11

I was a bit too late (again)
and Sunita's solution looks more elegant

No, you're never too old to Yak'n'Roll if you're too young to die.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 07:19:51

Whew! that was close!! a mere 26 seconds!!!

lappin
Posting Yak Master

182 Posts

Posted - 2011-05-25 : 07:21:58

Thank you

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-25 : 11:46:54

I realize this has already been solved, but I was just pondering it a bit from the performance angle. So, here are two more alternatives for fun:
Val - FLOOR(Val) = 0
Val - ROUND(Val,0,1) = 0

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 12:18:49

Would there be any difference in performance at all? In each of the four different solutions, there is one function call (I assume modulus operator is a function call as well)

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-25 : 12:26:19

If there is it appears that retriving the data is far more costly that applaying a function (in this case). My initial thought was that FLOOR or ROUND might be more effecient since they don't have a conversion as they return the same datatype as the input datatype. Here is a quick script for comparison:

--drop table #t
--

;WITH 
Tens (N)     AS (SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL
                 SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9), 
Thousands(N) AS (SELECT t1.N FROM Tens t1 CROSS JOIN Tens t2 CROSS JOIN Tens t3), 
Millions (N) AS (SELECT t1.N FROM Thousands t1 CROSS JOIN Thousands t2 CROSS JOIN Thousands t3), 
Tally (N)    AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT 0)) FROM Millions)

-- Load a million rows into temp table
select TOP (1000000)
    CAST(CAST(ABS(CAST(CAST(NEWID() AS VARBINARY) AS INT)) AS DECIMAL(18,2)) / CAST(N AS DECIMAL(18,2)) AS DECIMAL(18,2)) AS Val
into
	#t
from
	Tally

declare @st datetime
DECLARE @HasFraction BIT

-- Start test

set @st = getdate()

select 
	@HasFraction = case when Val % 1 = 0 then 0 else 1 end
from
	#t a

select right(convert(varchar(30),getdate()-@st,121),12) AS Method_Mod

set @st = getdate()

select 
	@HasFraction = case when Val - convert(int, Val) = 0 then 0 else 1 end
from
	#t a


select right(convert(varchar(30),getdate()-@st,121),12) AS Method_IntDiff


set @st = getdate()

select 
	@HasFraction = case when Val - FLOOR(Val) = 0 then 0 else 1 end
from
	#t a

select right(convert(varchar(30),getdate()-@st,121),12) AS Method_Floor


set @st = getdate()

select 
	@HasFraction = case when Val - ROUND(Val,0,1) = 0 then 0 else 1 end
from
	#t a

select right(convert(varchar(30),getdate()-@st,121),12) AS Method_Round

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-25 : 12:34:53

This is slightly faster
select
@HasFraction = case when Val = convert(int,val) then 0 else 1 end
from
#t a

select right(convert(varchar(30),getdate()-@st,121),12) AS cint

Jim

Awesome and fast script Lamprey!

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 12:44:13

Even though the difference is small, FLOOR seems to outperform all the other 3 CONSISTENTLY when I ran it several times with different number of rows etc. But, ROUND didn't seem any better than the rest.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2011-05-25 : 13:01:11

Thanks Jim,

Yeah, if I change things around like Jim showed:
when Val = convert(int,val)
when Val = floor(val)
etc..

It shaves a little bit off and the FLOOR/ROUND seem to be closer.. But, I also see the FLOOR method performing ever so slightly faster than then others.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-25 : 13:12:05

Hmmm, I was running mine on 2005 and the convert int was faster. When I ran it on 2008, the mod was always slightly faster.

Jim

Everyday I learn something that somebody else already knew

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-25 : 14:01:54

Meh! It's your computer trying to trick you and confuse you, Jim. I declare FLOOR as the winner.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-25 : 14:06:44

I don't know. I'm 1055 posts smarter than you are, and Lamprey is only 436 posts smarter than I. Good thing Webfred dropped the thread!

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources