## Remove Decimals Without Rounding

 Author Message ted montoya SSC Veteran Group: General Forum Members Points: 285 Visits: 21 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-SQLThanks for any helpTed Janet Keith-489067 SSC Eights! Group: General Forum Members Points: 838 Visits: 85 declare @mynum decimal(10,2)declare @mynum1 intset @mynum = 10.73select @mynum1 = @mynum * 100print @mynumprint @mynum1or declare @mynum decimal(10,2)set @mynum = 10.73select cast(@mynum * 100 as int) ted montoya SSC Veteran Group: General Forum Members Points: 285 Visits: 21 Thanks for your prompt answer. I was looking for something that would get rid of the decimal pointif the number was 10.73 or 10.733 or 10.7333 or 10.7The decimal place being in any positionThanksTed Janet Keith-489067 SSC Eights! Group: General Forum Members Points: 838 Visits: 85 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 Veteran Group: General Forum Members Points: 285 Visits: 21 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 meAgain, thank you so much for your generous helpTed Dinakar Nethi-176633 Hall of Fame Group: General Forum Members Points: 3281 Visits: 188 declare @i decimal(10,2)Set @i = 1078.734select (left(@i, Charindex('.', @i, 1) - 1 )) ******************Dinakar NethiLife is short. Enjoy it.****************** Jeff Moden SSC Guru Group: General Forum Members Points: 694837 Visits: 45622 Doesn't matter if it's varchar or not... your method still works...declare @i decimal(10,2)Set @i = 1078.734PRINT REPLACE(@i,'.','') --Jeff ModenRBAR 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 Helpful Links:How to post code problemsHow to post performance problemsForum FAQs ted montoya SSC Veteran Group: General Forum Members Points: 285 Visits: 21 Thank you all for your quick responsesI finally made it work with your helpTed Jeff Moden SSC Guru Group: General Forum Members Points: 694837 Visits: 45622 Cool... but it's customary to thank folks by posting the code that you used to make it work --Jeff ModenRBAR 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 Helpful Links:How to post code problemsHow to post performance problemsForum FAQs Tomm Carr Hall of Fame Group: General Forum Members Points: 3984 Visits: 787 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 precisionSet @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 loopSet @Result = Floor( @Working );While @Result < @Working begin Set @Working = @Working * 10; Set @Result = floor( @Working ); end--whileselect @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 "
" formatting back? This code IFCode shortcut sucks. Sure, the code goes into a nice text field  but everything is double spaced.                  			    				 			    Tomm Carr--Version Normal Form -- http://groups.google.com/group/vrdbms