Split integer valuefrom decimal valye

  • indraprakash

    SSChasing Mays

    Points: 637

    Hi All,

    I want to split integer part from decimal value

    e.g. i have a no is 12456324.254 and i want to the output is 12456324

    Note: We can't use convert function

  • ralfik

    Valued Member

    Points: 65

    Function floor() does not work? :unsure:

  • indraprakash

    SSChasing Mays

    Points: 637

    Thanks,

    It is working

  • PhilPacha

    Hall of Fame

    Points: 3525

    or, use

    ROUND(YourValue, 1)

    (revised) I'm sorry... I meant ROUND(YourValue, 0, 1)

    I apologize for my lack of proof-reading.

    The non-zero 2nd parameter truncates the value, without changing the datatype.

  • Jeff Moden

    SSC Guru

    Points: 994663

    indraprakash (7/7/2008)


    Note: We can't use convert function

    The ROUND function with the truncate option is definately the way to go... but why is it that you can't use the CONVERT function?

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • indraprakash

    SSChasing Mays

    Points: 637

    Hi,

    round function not gives proper output

    floor is right.

  • Jeff Moden

    SSC Guru

    Points: 994663

    indraprakash (7/8/2008)


    Hi,

    round function not gives proper output

    floor is right.

    Ummmm.... why do you think that? Using the optional 3rd parameter of ROUND makes it behave like FLOOR but with more control, if you need it.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • PhilPacha

    Hall of Fame

    Points: 3525

    I'm sorry... I meant ROUND(YourValue, 0, 1)

    I apologize for my lack of proof-reading.

  • Jeff Moden

    SSC Guru

    Points: 994663

    No problem and thanks for the feedback, Phil... I've made similar mistakes... just wanted to be sure...:)

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • vchandm23

    Old Hand

    Points: 304

    What if i want the other value not the floor value ...........

    i mean if its 1234.4321

    i want 4321 (values after the decimal place...)?

    Cheers,

    Chandra

  • vchandm23

    Old Hand

    Points: 304

    i know i can do the floor() on a decimal number and then store that to a variable and subtract it from the original number

    example

    @a = 2.75

    @B = floor(2.75)

    @C = @a - @B (this gives me 0.75)

    but is there a function that does this in SQL Server

    Cheers,

    Chandra

  • vchandm23

    Old Hand

    Points: 304

    i know i can do the floor() on a decimal number and then store that to a variable and subtract it from the original number

    example

    @a = 2.75

    @B = floor(2.75)

    @C = @a - @B (this gives me 0.75)

    but is there a function that does this in SQL Server

    Cheers,

    Chandra

  • Bruce W Cassidy

    SSChampion

    Points: 12607

    If you can't use convert(), you can always use cast(). BTW, I can't remember how floor() behaves for negative numbers, but I know there's a reason I end up using round() instead.

  • Jeff Moden

    SSC Guru

    Points: 994663

    vchandm23 (8/8/2011)


    but is there a function that does this in SQL Server

    Not in any version of SQL Server that I know of. You're pretty much stuck with doing the math like you did.

    --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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. 😉

    Helpful Links:
    How to post code problems
    Create a Tally Function (fnTally)

  • krs

    SSC Journeyman

    Points: 91

    vchandm23:

    What about using modulus (%)? Like some flavor of the following:

    select replace(1234.4321 % 1 ,'0.','')

Viewing 15 posts - 1 through 15 (of 15 total)

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