Remove Decimals Without Rounding

Question

Remove Decimals Without Rounding

Viewing 4 posts - 61 through 63 (of 63 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

Eugene Elutin (9/27/2012)
ChrisM@Work (9/27/2012)
Eugene Elutin (9/27/2012)
I have read a spec... 🙂
...
Obviously not this OP's spec! The input value is supposedly DECIMAL(5, 4), however this appears to clash with the quoted numbers in the first couple of pages. It's all tosh.
I did. And what I've said I cannot see a point in what OP trying to do (or at least in what I see everyone trying to achieve). That is one of the last OP posts:
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
I do understand that he wants to be able to remove decimal point from all possible numbers he gave us. What I don't think is useful is to get result as 1073, 10733, 107333 and 107.
I cannot see any possible use of the above at all!
Instead, (having in mind that his precision is 4), I think it should be something like 107300, 107330, 107333 and 107000.
I can be wrong, but would you think of any good use for the first one?
The second one is quite common requirement in finance related applications...

You're quite correct in questioning an OP's business requirement Eugene, because as often as not, that's where they've come unstuck - interpretation. Folks such as yourself with sufficient experience can see through the mess and figure out what the OP really wants to do, then show them the best way to do it.

In this case, many of the OP's sample numbers didn't even fit into a NUMERIC(5,1) and as one poster noticed, no rules were provided to deal with trailing 0's - which in most cases were also omitted from the OP's sample data. The nearest I came up with then - and now - is this:

SELECT

Input,

Step1 = REPLACE(Input,'.',''),

Step2 = REPLACE(REPLACE(Input,'.',''),'0',' '),

Step3 = RTRIM(REPLACE(REPLACE(Input,'.',''),'0',' ')),

Result = REPLACE(RTRIM(REPLACE(REPLACE(Input,'.',''),'0',' ')),' ','0')

FROM (SELECT Input = CAST(1.073 AS NUMERIC(5,4))) d

which trims trailing 0's. There's no guarantee that it's a satisfactory algorithm though, because the requirement wasn't ever properly nailed down.

This is a lose-lose situation :hehe:

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

vivekkumar341 Say Hey Kid Points: 661 More actions · Answer 2

Try this.

DECLARE

@ta VARCHAR(MAX) = '107.3'

SELECT SUBSTRING(@ta,0,CHARINDEX('.',@ta,0)) + SUBSTRING(@ta,CHARINDEX('.',@ta,0) + 1,LEN(@ta))

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

vivekkumar341 (9/27/2012)
Try this.
DECLARE
@ta VARCHAR(MAX) = '107.3'
SELECT SUBSTRING(@ta,0,CHARINDEX('.',@ta,0)) + SUBSTRING(@ta,CHARINDEX('.',@ta,0) + 1,LEN(@ta))

Why? How is it relevant to this thread? The OP isn't even around any more - he's a tuna fisherman in Madagascar.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Dwain Camps SSC Guru Points: 86908 More actions · Answer 4

I confess I haven't thoroughly read this entire thread but I don't think this approach was covered.

DECLARE @test-2 NUMERIC(5,4) = 1.55

SELECT REPLACE(REVERSE(0.+CAST(REVERSE(@Test + 0.) AS FLOAT)) COLLATE Latin1_General_BIN, '.', '')

I used something similar in this thread: http://www.sqlservercentral.com/Forums/Topic1340623-149-1.aspx and the timing results there were pretty good.

Not sure if the extra explicit CAST needed might drag it down though.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St