Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove Decimals Without Rounding


Remove Decimals Without Rounding

Author
Message
chan_dwr
chan_dwr
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 7
DECLARE @MYDECVAR DECIMAL

DECLARE @MYINTVAR INT

SET @MYDECVAR = 100.123

SET @MYINTVAR = CAST(@MYDECVAR AS INT)

SELECT @MYINTVAR



CHEERS Smile

CHANDRA
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22207 Visits: 9671
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
Ninja's_RGR'us
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22207 Visits: 9671
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
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51927 Visits: 40309
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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22207 Visits: 9671
For dropping leading and trailing 0s from the string. Maybe I just missed the problem altogether...



Must be having a bad day or something.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22207 Visits: 9671
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.
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22207 Visits: 9671
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 Smile but everything is double spaced. Angry












Sorry Tomm, I had missed your post. Your solution is much much workable than mine.
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)SSC Guru (51K reputation)

Group: General Forum Members
Points: 51927 Visits: 40309
Did someone say "loop"??? Blink On a single row????? Sick



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.
Although they tell us that they want it real bad, our primary goal is to ensure that we dont actually give it to them that way.
Although change is inevitable, change for the better is not.
Just because you can do something in PowerShell, doesnt mean you should. Wink

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22207 Visits: 9671
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?
Ninja's_RGR'us
Ninja's_RGR'us
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22207 Visits: 9671
Jeff as for the loop, I can appreciate a good mathematical solution. I wouldn't use it but I thaught it was neat.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search