Remove Decimals Without Rounding

Question

Remove Decimals Without Rounding

Viewing 15 posts - 46 through 60 (of 63 total)

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

John te Lintelo Say Hey Kid Points: 702 More actions · Answer 1

Peter Larsson (10/8/2007)
DECLARE@Original DECIMAL(10, 5)
SET@Original = 1078.734
SELECT@Original,
REPLACE(REPLACE(REPLACE(RTRIM(REPLACE(REPLACE(STR(@Original, 15, 5), ' ', '#'), '0', ' ')), ' ', '0'), '#', ''), '.', '')

Unfortunately the STR function takes a float as a parameter, losing the accuracy of a decimal.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 2

Do you have an example?

N 56°04'39.16"
E 12°55'05.25"

John te Lintelo Say Hey Kid Points: 702 More actions · Answer 3

Sure, the value 9876504321.1203456 as used in the examples above. Even after changing the code to use DECIMAL(38, 12) the result is 98765043211203461 instead of 98765043211203456.

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 4

OP (Ted Montoya) wrote 9/26/2007 10:04 PM that datatype is DECIMAL(5, 4).

All values I tested my code with works...

N 56°04'39.16"
E 12°55'05.25"

SwePeso SSC-Dedicated Points: 39757 More actions · Answer 5

DECLARE @Sample TABLE (d DECIMAL(5, 4))

DECLARE @original DECIMAL(6, 4)

SET @original = 0.0

WHILE @original < 10.0

BEGIN

INSERT @Sample

SELECT @original

SET @original = @original + 0.0001

END

SELECT d AS OrignalValue,

REPLACE(REPLACE(REPLACE(RTRIM(REPLACE(REPLACE(STR(d, 15, 5), ' ', '#'), '0', ' ')), ' ', '0'), '#', ''), '.', ''),

CAST(10000 * d AS INT)

FROM @Sample

ORDER BY d

N 56°04'39.16"
E 12°55'05.25"

John te Lintelo Say Hey Kid Points: 702 More actions · Answer 6

OK, based on the original question posed, your solution is valid.

I guess I got carried away and lost sight of the "requirements" :blush:

Aaron Curtis SSC Enthusiast Points: 170 More actions · Answer 7

If you're like me and want to clean up the decimal places during your select statement, you can use the following function set:

Replace(Left( as varchar))), '.', '') as YourAlias

This takes care of 0.00 values as well... hope it helps!

- Aaron Curtis

Aaron Curtis SSC Enthusiast Points: 170 More actions · Answer 8

Sorry the variable value got stripped out... here you go

If you're like me and want to clean up the decimal places during your select statement, you can use the following function set:

Replace(Left(decimalfield as varchar))), '.', '') as YourAlias

This takes care of 0.00 values as well... hope it helps!

- Aaron Curtis

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

Heh... yeah... several solutions similar to that were posted.

The remaining question I have is, what will this be used for? Without knowing were the decimal point WAS... the information is totally useless.

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

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

I was wondering the usefulness myself. Unless you store elsewhere data to put back in the decimal, I can't imagine the number being very useful. But I'm sure somebody can think of a use.

Even though the number generated seems to have no apparent use, that doesn't mean the challenge wasn't fun.

------

And not to toot my own horn, but with my solution you could easily capture and store the data needed to put the decimal place back in. 🙂

Kindest Regards,

--SF

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

deyvisonsouto Valued Member Points: 63 More actions · Answer 11

it's just use convert(int, <<value>>)

eq:

select convert(int,2.5)

result:

2

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

deyvisonsouto (9/27/2012)
it's just use convert(int, <<value>>)
eq:
select convert(int,2.5)
result:
2

Nope! Miles off. Did you read the spec?

^{“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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 13

I have read a spec... 🙂

Now, I cannot see any value in a function which removes decimal point in a way it does.

It's just my guess, but I'm somehow sure that the number of decimal points needs to be constant. Otherwise, the results are quite useless:

12.001 will become 12001

and

12.99 will become 1299

I've tried to think over, but failed to find any good use for this sort of behaviour.

Instead I think that the function should be able to remove point and stay with required precision (length of the decimal part), so

with required precision of 3

12.001 will become 12001

and

12.99 will become 12990

and with required precision of 2

12.001 will become 1200

and

12.99 will become 1299

so, I would just go with something like:

SELECT CAST(@InputAnyDecimal * POWER(10,@RequiredPrecision) AS DECIMAL(38,0))

You may want to replace DECIMAL(38,0) with INT or BIGINT if it is appropriate in your case.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

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

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.

^{“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

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 15

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]