Remove Decimals Without Rounding

Question

Post reply

Remove Decimals Without Rounding

Viewing 15 posts - 16 through 30 (of 63 total)

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

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

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 SSC Guru Points: 294069 More actions · Answer 2

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. :angry:

Sorry Tomm, I had missed your post. Your solution is much much workable than mine.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 3

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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 4

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 SSC Guru Points: 294069 More actions · Answer 5

Jeff as for the loop, I can appreciate a good mathematical solution. I wouldn't use it but I thaught it was neat.

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 6

Ninja's_RGR'us (9/29/2007)
So Jeff, how do you solve this version of the problem?
Set @i = 0.034
PRINT REPLACE(@i,'.','')
--003400 OR 34?
Set @i = 0.0304
PRINT REPLACE(@i,'.','')
--003040 OR 304?

I assume you're talking about adding leading zero's... figure out how many you want by changing the "15"...:P

DECLARE @i DECIMAL(10,5)

SET @i = .034

PRINT REPLACE(REPLACE(STR(@i,15,5),'.',''),' ','0')

PRINT REPLACE(REPLACE(STR(@i,10,5),'.',''),' ','0')

PRINT REPLACE(REPLACE(STR(@i,5,5),'.',''),' ','0')

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 7

Oh... and do be aware that STR uses FLOAT for it's main operand... 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 8

I just want to point out that my solution was provided with the idea that leading and trailing 0 had to be trimmed. I'm curious to see what you guys can come up with. Anyone up for the challenge?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 9

Because of decimal (x,2), you lose the last digit from the value. Then the convert to int kills the rest of the decimals, this is not what is asked here. You must keep everything but the pedriod, period.

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 10

Jeff Moden (9/29/2007)
Did someone say "loop"??? :blink: On a single row????? :sick:

Don't let (y)our antipathy toward row looping turn you away from all looping. Many times it is appropriate, especially when there is no data access within the loop. It is strictly a mathematical loop. You can touch it. It won't hurt you.

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

But there was no requirement that it be padded with zeros. In the original examples, there were no trailing zeros.

Why do you need a loop????

Even your solution has a loop. The Replace function must loop through each character position in the string. Just because a loop has been abstracted from sight doesn't mean it isn't still there. My loop only repeats for each significant digit to the right of the decimal point. So, I have to loop an average of about half as many times as Replace.

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

S. Frog SSC Eights! Points: 886 More actions · Answer 11

Now keep in mind, I'm still kind of a newbie, but this seems to work ok. Though it gacks if you use more than 9 digits before or more than 6 digits after the decimal place. But I'm sure a more experienced person can point out where that can be fixed.

Declare @num float(38)

Set @num = 987654321.123456

select @num*(power(10,(len(@num-(cast(@num as int)))-2)))

If you want to step out each action, this might help:

select num, -- original number

cast(@num as int), -- original number without decimals

@num-cast(@num as int), -- just the decimals

len(@num-cast(@num as int))-2, -- how many decimal places

power(10,(len(@num-(cast(@num as int)))-2)), -- 10 to the power of the number of decimal places

@num*(power(10,(len(@num-(cast(@num as int)))-2))) -- I'm gonna wash that decimal right out of my hair...

What do you think - good, bad, or SQL isn't for people with Anthro degrees?

--S. Frog

Kindest Regards,

--SF

(SELECT COALESCE(Phlogiston,Caloric,DarkMatter) as Construct FROM Science.dbo.Theory)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

I love it... this is why we need that many members here!

Tomm Carr SSCertifiable Points: 5426 More actions · Answer 13

Just for grins, I compared two methods.

Method 1: No (explicit) looping.

Set @Result = Replace( RTrim( Replace( Replace( Convert( varchar, @original ), '.', '' ), '0', ' ' ) ), ' ', '0' );

and Method 2: looping

While @Result < @Working

begin

Set @Working = @Working * 10;

Set @Result = floor( @Working );

end--while

Both produced the same results but Method 2 executed in about 1/3 the time of Method 1 (9.3 microsecs vs. 3.0 microsec).

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

Jeff Moden SSC Guru Points: 1004432 More actions · Answer 14

Post the entire test harness so we can do the same test, Tom 😉

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

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 15

Quoting myself : Sorry Tomm, I had missed your post. Your solution is much much workable than mine.

However, I too would like to see the whole test. Math operations are most of the time faster than string ops. This seems to fall under the same category.