Floats with no scientific notation

Hello,
I've been searching the groups for a way to accomplish this, but I've
come up short. What I ultimately need is a way to count the number of
decimal places in a floating point number (ie 37.88955 ==> 5, 0.02 ==>
2, etc).
What I'm currently working on is how to convert a float to decimal,
with no scientific notation.
cast(cast(f.formula_percent as decimal(20,10)) as varchar(20))
This almost does the trick, except that I also need to trim off the
trailing 0's (ie 0.02000000 -> 0.02 ==> 2).
My data has anywhere from 0 to 7 decimals, so I need a way to calculate
this dynamically.
Any Ideas?
Thanks in advance.
Brian AckermannThere have been many posts on the topic of floating point values and how
they are represented in a binary format. Simply put, what you are asking is
impossible since floating point datatypes are inherently inaccurate. There
was a post today with the topic "float return" that begins to delve into the
subject.
The short answer is to change the way you store the data if you need to
"see" the exact value that was entered. Otherwise, you will need to use
some algorithm based on an understanding of the floating point storage issue
and an analysis of the domain represented by the table/column. Something
along the lines of [convert from float to 7 place decimal number and
evaluate the result] - any logic that you do develop will be, by design, of
limited accuracy. Below is a query that you can use for testing (thanks to
Steve Kass via the thread mentioned earlier).
select cast (16.9 as float), cast (16.89999 as float)
Incidentally, presentation of data is best left to a client application.
"Brian Ackermann" <brian.ackermann@.gmail.com> wrote in message
news:1113326461.535229.251350@.f14g2000cwb.googlegroups.com...
> Hello,
> I've been searching the groups for a way to accomplish this, but I've
> come up short. What I ultimately need is a way to count the number of
> decimal places in a floating point number (ie 37.88955 ==> 5, 0.02 ==>
> 2, etc).
> What I'm currently working on is how to convert a float to decimal,
> with no scientific notation.
> cast(cast(f.formula_percent as decimal(20,10)) as varchar(20))
> This almost does the trick, except that I also need to trim off the
> trailing 0's (ie 0.02000000 -> 0.02 ==> 2).
> My data has anywhere from 0 to 7 decimals, so I need a way to calculate
> this dynamically.
> Any Ideas?
> Thanks in advance.
> Brian Ackermann
>|||Scott,
I agree with what you say. However, in my case, there are other
circumstances. For one, this is not a data presentation issue. I'm
actually adding functionality to an existing db, adding a seperate
column for the lab to be able to specify per line item precision. This
problem arises when trying to fill in this new field with data from the
table we are trying to make the precision adjustments on.
For the record, by trial and error I assembled the following. It
works, though I'm sure it does not do the job very well. Perhaps
others may find it useful too.
update formula
set Significant_Figures =
CASE
WHEN patindex('0000000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 14
WHEN patindex('000000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 13
WHEN patindex('00000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 12
WHEN patindex('0000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 11
WHEN patindex('000000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 10
WHEN patindex('00000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 9
WHEN patindex('0000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 8
WHEN patindex('000000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 7
WHEN patindex('00000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 6
WHEN patindex('0000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 5
WHEN patindex('000', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 4
WHEN patindex('00', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 3
WHEN patindex('0', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 2
WHEN patindex('', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 1
WHEN patindex('%0', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20))) > 0
THEN len(cast(cast(f.formula_percent as decimal(20,10)) as
varchar(20))) - 1 - charindex('.', cast(cast(f.formula_percent as
decimal(20,10)) as varchar(20))) - 0
WHEN patindex('%', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20))) > 0
THEN patindex('%', cast(cast(f.formula_percent as decimal(20,10))
as varchar(20)))
ELSE 'foo' --never happens
END
from formula f
Thanks,
Brian|||On 12 Apr 2005 11:46:55 -0700, Brian Ackermann wrote:
(snip)
>For the record, by trial and error I assembled the following. It
>works, though I'm sure it does not do the job very well. Perhaps
>others may find it useful too.
Hi Brian,
If you tested it, then I have no reason to doubt your statement that
this works - but why so complicated? Consider replacing the lengthy CASE
expression with a simple expression:
declare @.a float
set @.a = 37.800954
select len(rtrim(replace(cast(@.a as decimal(20,10)),'0',' ')))
- charindex('.',cast(@.a as decimal(20,10)))
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I work strictly on geochemical and/or hydrogeological databases in
Environmental Consulting. I have struggled for years with presenting number
s
with the correct number of significant figures, including trailing 0's(i.e.
1.00 is 1.00, not 1). The best fix that I have come up with was to create
corresponding text columns for my number fields. i.e. Result and Result_tex
t
I use this sp to populate the Result_text column
create proc update_result_text as
update lab_results
set result_text =
CASE
WHEN sig_figs + floor(-log10(abs(result))) < 0 THEN
-- The result will have trailing zeros - round first and then
use str
STR(round(result, cast(sig_figs + floor(-log10(abs(result))) as
int)), 10, 0)
ELSE
-- The result will have no trailing zeros - use str directly
STR(result, 10, cast(sig_figs + floor(-log10(abs(result))) as
int))
END
There is no way to count the number of decimal places in a floating point
number, but you can use the floor(-log10(abs(result))) function to find the
order of magnitude of the number you are looking for.
Archer
"Brian Ackermann" wrote:

> Hello,
> I've been searching the groups for a way to accomplish this, but I've
> come up short. What I ultimately need is a way to count the number of
> decimal places in a floating point number (ie 37.88955 ==> 5, 0.02 ==>
> 2, etc).
> What I'm currently working on is how to convert a float to decimal,
> with no scientific notation.
> cast(cast(f.formula_percent as decimal(20,10)) as varchar(20))
> This almost does the trick, except that I also need to trim off the
> trailing 0's (ie 0.02000000 -> 0.02 ==> 2).
> My data has anywhere from 0 to 7 decimals, so I need a way to calculate
> this dynamically.
> Any Ideas?
> Thanks in advance.
> Brian Ackermann
>|||It sounds like you might want to look at DECIMAL(s,p) and NUMERIC(s,p)
data types instead of FLOAT and REAL. The advantage of a floating
point representation is that can get it on a co-processor chip for lab
work. The advantage of a BCD representation is that there is limited
rounding errors within the range. but nobody makes BCD co-processor
chips that I know about; Burroughs had something like this in one of
their COBOL machines.
And throw in the usual lecture about doign display in the front end,
not the database.