Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Remove Decimals Without Rounding Expand / Collapse
Author
Message
Posted Wednesday, September 26, 2007 12:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 4, 2012 10:48 AM
Points: 5, Visits: 16
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
Post #403180
Posted Wednesday, September 26, 2007 1:19 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2008 11:53 AM
Points: 46, Visits: 85
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)
Post #403197
Posted Wednesday, September 26, 2007 1:54 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 4, 2012 10:48 AM
Points: 5, Visits: 16
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
Post #403210
Posted Wednesday, September 26, 2007 2:10 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 7, 2008 11:53 AM
Points: 46, 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,'.','')
Post #403217
Posted Wednesday, September 26, 2007 3:04 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 4, 2012 10:48 AM
Points: 5, Visits: 16
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
Post #403236
Posted Wednesday, September 26, 2007 3:29 PM


SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Thursday, December 5, 2013 12:21 PM
Points: 265, Visits: 164
declare @i decimal(10,2)
Set @i = 1078.734
select (left(@i, Charindex('.', @i, 1) - 1 ))


******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Post #403240
Posted Thursday, September 27, 2007 12:02 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #403351
Posted Thursday, September 27, 2007 3:58 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 4, 2012 10:48 AM
Points: 5, Visits: 16
Thank you all for your quick responses
I finally made it work with your help
Ted
Post #403809
Posted Thursday, September 27, 2007 7:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:09 AM
Points: 35,348, Visits: 31,886
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #403860
Posted Friday, September 28, 2007 12:19 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 4:47 PM
Points: 501, Visits: 784
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.


Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
Post #404193
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse