Post reply

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

  • 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!!

  • January 21, 2014 at 12:23 am

    #1681746

    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

  • January 21, 2014 at 1:35 am

    #1681762

    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)

  • January 21, 2014 at 2:34 am

    #1681779

    Thanks GilaMonster and thanks Jeff!!

  • January 21, 2014 at 9:09 am

    #1682021

    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

  • January 21, 2014 at 9:40 am

    #1682061

    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

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

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