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: