SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Remove Decimals Without Rounding


Remove Decimals Without Rounding

Author
Message
ted montoya
ted montoya
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 21
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
Janet Keith-489067
Janet Keith-489067
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 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)
ted montoya
ted montoya
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 21
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
Janet Keith-489067
Janet Keith-489067
SSC-Enthusiastic
SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)SSC-Enthusiastic (170 reputation)

Group: General Forum Members
Points: 170 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,'.','')
ted montoya
ted montoya
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 21
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
Dinakar Nethi-176633
Dinakar Nethi-176633
SSC Eights!
SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)SSC Eights! (819 reputation)

Group: General Forum Members
Points: 819 Visits: 188
declare @i decimal(10,2)

Set @i = 1078.734

select (left(@i, Charindex('.', @i, 1) - 1 ))

******************
Dinakar Nethi
Life is short. Enjoy it.
******************
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

Group: General Forum Members
Points: 155209 Visits: 41777
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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
ted montoya
ted montoya
SSC Rookie
SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)SSC Rookie (49 reputation)

Group: General Forum Members
Points: 49 Visits: 21
Thank you all for your quick responses

I finally made it work with your help

Ted
Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)SSC Guru (155K reputation)

Group: General Forum Members
Points: 155209 Visits: 41777
Cool... but it's customary to thank folks by posting the code that you used to make it work Wink

--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.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
Tomm Carr
Tomm Carr
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1166 Visits: 787
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

Tomm Carr
--
Version Normal Form -- http://groups.google.com/group/vrdbms
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