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 Saturday, September 29, 2007 1:25 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, March 14, 2014 8:04 PM
Points: 7, Visits: 7
DECLARE @MYDECVAR DECIMAL
DECLARE @MYINTVAR INT
SET @MYDECVAR = 100.123
SET @MYINTVAR = CAST(@MYDECVAR AS INT)
SELECT @MYINTVAR

CHEERS :)
CHANDRA
Post #404461
Posted Saturday, September 29, 2007 2:15 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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.
Post #404465
Posted Saturday, September 29, 2007 2:37 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
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')
Post #404471
Posted Saturday, September 29, 2007 3:56 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:01 PM
Points: 35,372, Visits: 31,923
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."

(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 #404489
Posted Saturday, September 29, 2007 4:18 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
For dropping leading and trailing 0s from the string. Maybe I just missed the problem altogether...

Must be having a bad day or something.
Post #404497
Posted Saturday, September 29, 2007 4:30 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Cancel that, I'm having a great day, this is why I came up with this solution :

declare @i decimal(10,5)
Set @i = 1078.734
PRINT REPLACE(@i,'.','')
--What's needed? 107873400 or 1078734

Set @i = 0.034
PRINT REPLACE(@i,'.','')
--003400 OR 34?

Set @i = 0.0304
PRINT REPLACE(@i,'.','')
--003040 OR 304?


--Now Jeff, let's see your solution to crap out those leading and trailing 0s.
Post #404502
Posted Saturday, September 29, 2007 4:38 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Tomm Carr (9/28/2007)
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.






Sorry Tomm, I had missed your post. Your solution is much much workable than mine.
Post #404504
Posted Saturday, September 29, 2007 9:06 PM


SSC-Dedicated

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

Group: General Forum Members
Last Login: Today @ 7:01 PM
Points: 35,372, Visits: 31,923
Did someone say "loop"??? On a single row?????

Using a previous example where 5 decimal places are required to be padded with zeros...

DECLARE @i DECIMAL(10,5)
SET @i = 1078.734
PRINT REPLACE(STR(@i,15,5),'.','')

Why do you need a loop????


--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 #404545
Posted Saturday, September 29, 2007 9:23 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
So Jeff, how do you solve this version of the problem?

declare @i decimal(10,5)
Set @i = 1078.734
PRINT REPLACE(@i,'.','')
--What's needed? 107873400 or 1078734

Set @i = 0.034
PRINT REPLACE(@i,'.','')
--003400 OR 34?

Set @i = 0.0304
PRINT REPLACE(@i,'.','')
--003040 OR 304?
Post #404557
Posted Saturday, September 29, 2007 9:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Jeff as for the loop, I can appreciate a good mathematical solution. I wouldn't use it but I thaught it was neat.
Post #404558
« Prev Topic | Next Topic »

Add to briefcase ««12345»»»

Permissions Expand / Collapse