I'm having trouble identifying a column that is a float data type that uses
exponential notation. I've tried Casting and Converting to various data
types in order to find the 'E' but the 'E' never comes through. Is there a
better way to identify any float that displays with exponential notation?
TIA
Matt
The float column is stored as a binary number with a binary exponent.
It may be displayed with an E, but this is simply the way the front
end shows it. Internally there is no E.
The size at which a float is displayed using the E notation is built
into the front end program performing the display. It is not an
attribute of SQL Server.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 10:57:21 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I'm having trouble identifying a column that is a float data type that uses
>exponential notation. I've tried Casting and Converting to various data
>types in order to find the 'E' but the 'E' never comes through. Is there a
>better way to identify any float that displays with exponential notation?
>TIA
>Matt
>
|||> The size at which a float is displayed using the E notation is built
> into the front end program performing the display. It is not an
> attribute of SQL Server.
I understand that, but I still need to identify those that display using exp
notation. What is the internal mechanism that causes SQL server to display
the number as exponential? In our case it is negative numbers with a decimal
and a leading 0. The issue I'm facing is that someone queried a table,
copied and pasted the results into a text file and imported the data into a
program. The columns that had exponential notation are off by 2-3 decimal
places because of that. There were probably a hundred thousand rows
extracted and the data was heavily manipulated prior to the import. There
are relatively few rows that had the data in exp notation but they have a
major effect on the dataset. It would be much easier to re-query the data
and cast it to varchar or decimal and just extract and import those rows
instead of trying to do the whole thing (which was about a weeks worth of
work) again but in order to do that, I need a way to identify the rows where
that field is exponential notation. So, if there is ANY way to determine if
a float will display using exponential notation, I'd really like to know
how. If anyone has other suggestions on how to handle this situation whether
it be SQL or otherwise, I'm open to suggestions.
TIA
Matt
|||If you use a client that displays floats in scientific notation (which
is quite common), and this notation is a problem, then you shouldn't use
float.
One way to do that, is to create a view that casts the float to a
decimal, and instruct your users / applications to use that view (and/or
disallow selects from the base table).
As mentioned by Roy, the value that is transferred to the client does
not contain an "E".
Gert-Jan
Matt Williamson wrote:
>
> I understand that, but I still need to identify those that display using exp
> notation. What is the internal mechanism that causes SQL server to display
> the number as exponential? In our case it is negative numbers with a decimal
> and a leading 0. The issue I'm facing is that someone queried a table,
> copied and pasted the results into a text file and imported the data into a
> program. The columns that had exponential notation are off by 2-3 decimal
> places because of that. There were probably a hundred thousand rows
> extracted and the data was heavily manipulated prior to the import. There
> are relatively few rows that had the data in exp notation but they have a
> major effect on the dataset. It would be much easier to re-query the data
> and cast it to varchar or decimal and just extract and import those rows
> instead of trying to do the whole thing (which was about a weeks worth of
> work) again but in order to do that, I need a way to identify the rows where
> that field is exponential notation. So, if there is ANY way to determine if
> a float will display using exponential notation, I'd really like to know
> how. If anyone has other suggestions on how to handle this situation whether
> it be SQL or otherwise, I'm open to suggestions.
> TIA
> Matt
|||There is no single answer because this is not done by SQL Server but
by whatever front-end program processes the result of the SELECT.
>The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that.
All I can suggest is find out what tool that person used, along with
whatever configuration settings were in place, and experiment until
you understand what that tool does to display exponential data. If
they repeat that very first step perhaps you can pick the problems out
there.
Roy Harvey
Beacon Falls, CT
On Wed, 15 Aug 2007 13:19:47 -0400, "Matt Williamson"
<ih8spam@.spamsux.org> wrote:
>I understand that, but I still need to identify those that display using exp
>notation. What is the internal mechanism that causes SQL server to display
>the number as exponential? In our case it is negative numbers with a decimal
>and a leading 0. The issue I'm facing is that someone queried a table,
>copied and pasted the results into a text file and imported the data into a
>program. The columns that had exponential notation are off by 2-3 decimal
>places because of that. There were probably a hundred thousand rows
>extracted and the data was heavily manipulated prior to the import. There
>are relatively few rows that had the data in exp notation but they have a
>major effect on the dataset. It would be much easier to re-query the data
>and cast it to varchar or decimal and just extract and import those rows
>instead of trying to do the whole thing (which was about a weeks worth of
>work) again but in order to do that, I need a way to identify the rows where
>that field is exponential notation. So, if there is ANY way to determine if
>a float will display using exponential notation, I'd really like to know
>how. If anyone has other suggestions on how to handle this situation whether
>it be SQL or otherwise, I'm open to suggestions.
>TIA
>Matt
>
