How to check wheter a value is whole number or in fraction

Question

Post reply

How to check wheter a value is whole number or in fraction

avdhut.k

SSCrazy

Points: 2834
More actions
January 21, 2014 at 12:20 am

#396133

Hi,

Declare @number as numeric(4,2) = 2.5

Select @number

My requirement is I want to check whether @number is whole number or in fraction.

Thanks in Advance!!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

DECLARE @number AS NUMERIC(4,2) = 2.5;



SELECT @number;

IF (ROUND(@number,0) = @number)

	PRINT 'whole number';

ELSE

PRINT 'has fractional component';

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Borrowing a bit of Gail's code, pick your poison. Just remember that the prerounding of the datatype you've chosen is going to result in some possibly surprising answers if you pass it numbers like 2.999.

DECLARE @number AS NUMERIC(4,2) ; SELECT @number = 2.99;



print @number;

IF (ROUND(@number,0) = @number)

	PRINT 'whole number';

ELSE

	PRINT 'has fractional component';

IF @number%1 = 0

	PRINT 'whole number';

ELSE

	PRINT 'has fractional component';

IF CAST(@Number AS INT) = @Number

	PRINT 'whole number';

ELSE

	PRINT 'has fractional component';

IF (ROUND(@number,0,1) = @number)

	PRINT 'whole number';

ELSE

	PRINT 'has fractional component';

IF @number LIKE '%.%[1-9]%'

	PRINT 'has fractional component';

ELSE

	PRINT 'whole number';

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

avdhut.k SSCrazy Points: 2834 More actions · Answer 3

avdhut.k

SSCrazy

Points: 2834

January 21, 2014 at 2:34 am

#1681779

Thanks GilaMonster and thanks Jeff!!

Luis Cazares SSC Guru Points: 183686 More actions · Answer 4

A couple of additional methods 🙂



IF (FLOOR(@number) = @number)

	PRINT 'whole number';

ELSE

	PRINT 'has fractional component';

IF (CEILING(@number) = @number)

	PRINT 'whole number';

ELSE

	PRINT 'has fractional component';

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 5

Back before we had all of these fancy functions I used this method:



DECLARE @number AS NUMERIC(4,2) ; SELECT @number = 2.99;

print @number;

if cast(@number as int) = @number

begin

	Print 'whole number'

end

else

	print 'has fractional component'

I do like the FLOOR/CEILING functions.

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman