Remove Decimals Without Rounding

  • ted montoya

    SSC-Addicted

    Points: 499

    How do I take a number like maybe 10.73 and remove the decimal place and making it a number like 1073. In other words I have to remove the decimal.

    I need to do it using T-SQL

    Thanks for any help

    Ted

  • Janet Keith-489067

    Ten Centuries

    Points: 1042

    declare @mynum decimal(10,2)

    declare @mynum1 int

    set @mynum = 10.73

    select @mynum1 = @mynum * 100

    print @mynum

    print @mynum1

    or

    declare @mynum decimal(10,2)

    set @mynum = 10.73

    select cast(@mynum * 100 as int)

  • ted montoya

    SSC-Addicted

    Points: 499

    Thanks for your prompt answer.

    I was looking for something that would get rid of the decimal point

    if the number was 10.73 or 10.733 or 10.7333 or 10.7

    The decimal place being in any position

    Thanks

    Ted

  • Janet Keith-489067

    Ten Centuries

    Points: 1042

    what is the datatype of the field containing the decimal point?

    If it's varchar you could:

    declare @mynum varchar(20)

    set @mynum = '10.7333'

    select replace(@mynum,'.','')

  • ted montoya

    SSC-Addicted

    Points: 499

    The field is numeric(5,4)

    but you have something there.

    would it be possible to move it to a char field and then apply the logic you gave me

    Again, thank you so much for your generous help

    Ted

  • Dinakar Nethi-176633

    SSCarpal Tunnel

    Points: 4629

    declare @i decimal(10,2)

    Set @i = 1078.734

    select (left(@i, Charindex('.', @i, 1) - 1 ))

    ******************
    Dinakar Nethi
    Life is short. Enjoy it.
    ******************

  • Jeff Moden

    SSC Guru

    Points: 993641

    Doesn't matter if it's varchar or not... your method still works...

    declare @i decimal(10,2)

    Set @i = 1078.734

    PRINT REPLACE(@i,'.','')

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

  • ted montoya

    SSC-Addicted

    Points: 499

    Thank you all for your quick responses

    I finally made it work with your help

    Ted

  • Jeff Moden

    SSC Guru

    Points: 993641

    Cool... but it's customary to thank folks by posting the code that you used to make it work 😉

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

  • Tomm Carr

    SSCertifiable

    Points: 5286

    The problem with using the Replace string function is that 10.341 ends up as 1034100 if the numeric is defined with five places. If you want 10.342 to end up as 10341, 10.73 as 1073, 10.733 as 10733, 10.7333 as 107333 and 10.7 as 107, then the best way is to use numeric processes rather than string.

    The only thing is, you have to define your working number to have enough places to the left to contain the entire value. So your example of storing 10.341 into a variable defined as decimal(5,4) is bogus--it only has one place to the left of the decimal point. So if your value is defined as "decimal(x,y)" then you have to declare a working variable as "decimal(x+y,y)" to contain the entire finished value.

    declare @Original decimal( 10, 5 ),

    @Working decimal( 15, 5 ), -- 10 + 5 = 15

    @Result decimal( 15, 0 ); -- Doesn't need scale, only precision

    Set @Original = 1078.734; -- This would be, say, an input parameter

    -- First, make a copy into the working variable capable of handling it.

    Set @Working = @Original;

    -- Now set up the loop

    Set @Result = Floor( @Working );

    While @Result < @Working

    begin

    Set @Working = @Working * 10;

    Set @Result = floor( @Working );

    end--while

    select @Result as Result, @Working as Working;The loop executes one time through for each significant digit to the right of the decimal point -- in this example, three times. The result is 1078734 instead of 107873400.

    As an aside, does anyone know how to get the old "<pre></pre>" formatting back? This code IFCode shortcut sucks. Sure, the code goes into a nice text field 🙂 but everything is double spaced. :angry:

    Tomm Carr
    --
    Version Normal Form -- http://groups.google.com/group/vrdbms

  • chan_dwr

    SSC Rookie

    Points: 49

    DECLARE @MYDECVAR DECIMAL

    DECLARE @MYINTVAR INT

    SET @MYDECVAR = 100.123

    SET @MYINTVAR = CAST(@MYDECVAR AS INT)

    SELECT @MYINTVAR

    CHEERS 🙂

    CHANDRA

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    DECLARE @MYDECVAR DECIMAL

    DECLARE @MYINTVAR INT

    SET @MYDECVAR = 100.123

    SET @MYINTVAR = CAST(@MYDECVAR AS INT)

    SELECT @MYINTVAR

    This returns 100, not 100123 as it should.

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    This should do the trick... 100% of the time :

    DECLARE @Val DECIMAL (11,8)

    SET @Val = 100.10300100

    SELECT REPLACE(RTRIM(LTRIM(REPLACE(REPLACE(CONVERT(VARCHAR(30), @Val), '.', ''), '0', ' '))), ' ', '0')

    SET @Val = 0.01

    SELECT REPLACE(RTRIM(LTRIM(REPLACE(REPLACE(CONVERT(VARCHAR(30), @Val), '.', ''), '0', ' '))), ' ', '0')

    SET @Val = 1

    SELECT REPLACE(RTRIM(LTRIM(REPLACE(REPLACE(CONVERT(VARCHAR(30), @Val), '.', ''), '0', ' '))), ' ', '0')

    SET @Val = 0010.20301

    SELECT REPLACE(RTRIM(LTRIM(REPLACE(REPLACE(CONVERT(VARCHAR(30), @Val), '.', ''), '0', ' '))), ' ', '0')

  • Jeff Moden

    SSC Guru

    Points: 993641

    Why do you think you need the extra overhead of RTRIM/LTRIM when converting Decimal to Varchar?

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

  • Ninja's_RGR'us

    SSC Guru

    Points: 294069

    For dropping leading and trailing 0s from the string. Maybe I just missed the problem altogether...

    Must be having a bad day or something.

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

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