Solved

Handling Scientific Notation in SQL Server table

Posted on 2007-11-26
10
5,888 Views
Last Modified: 2013-11-30
I am loading data into SQL Server using DTS.  That seems to be working fine.  Many of the values are in scientific notation, ie. 1.00E-005, -6.63E-011, etc.   I set the decimal datatype with precision 28 and scale 14 to handle storing the values correctly and that works.  However, I just found out that they prefer to keep the scientific notation in the field value, so they want 1.00E-005, not the expanded decimal version.  Some people may read the tables directly to work with the data and they don't want these users to have to convert the data back to scientific notation.  Does anyone know how to handle this or is loading as a decimal as I have done the only way to do it?
Thanks,
Alexis
0
Comment
Question by:alexisbr
  • 5
  • 2
  • 2
  • +1
10 Comments
 
LVL 14

Assisted Solution

by:NBSO_ISS
NBSO_ISS earned 150 total points
ID: 20351864
You could load it as a VARCHAR data type, but you would have to convert it to a numeric type to do calculations on it.
0
 
LVL 92

Accepted Solution

by:
Patrick Matthews earned 200 total points
ID: 20351874
Hello alexis,

Depends on what needs to happen with the data.  If *any* arithmetical operations are going to
be done, it is best to store the data as decimal (as you've done), and then format the output
as need be on queries or reports.

The only way to "store" data in scientific notation is to store it as a "string" type such as varchar,
but then that makes the data clumsy to use for any kind of math or sorting.

Regards,

Patrick
0
 
LVL 69

Expert Comment

by:Scott Pletcher
ID: 20352782
Use FLOAT or REAL for the datatype; SQL will format the output in scientific notation when applicable.
0
Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

 

Author Comment

by:alexisbr
ID: 20352811
Thanks to both of you.  Since we will be doing calculations on the data, I will  store it as a decimal as I have done but will format the output in data dumps for the users.  

Regards,
Alexis
0
 

Author Comment

by:alexisbr
ID: 20352866
Hi Scott,
I just saw your post.  I have now changed one of the fields to FLOAT but when I display the value in a view, I still see all the decimal places and not scientific notation.  In what cases will SQL format the value automatically?
Thanks,
Alexis
0
 
LVL 69

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 150 total points
ID: 20352997
It depends on the specific value of the data: if it's too small/large, SQL automically uses scientific notation.

You can also use CONVERT() to force SQL to use scientific notation:

SELECT CONVERT(VARCHAR(16), columnName, #)
Where # is 0, 1 or 2, as follows:
0 (default) Six digits maximum. Use in scientific notation, when appropriate.
1 Always eight digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.

So, to see a sample of each format, do this:

SELECT TOP 100
    CONVERT(VARCHAR(16), columnName, 0),
    CONVERT(VARCHAR(16), columnName, 1),
    CONVERT(VARCHAR(16), columnName, 2)
FROM tableName
0
 

Author Comment

by:alexisbr
ID: 20354344
Hi Scott,
Now I see what you mean.  Where I was earlier, when I changed the decimal to float, nothing happened.  But that was on SQL Server 2000.  I just tested on SQL Server 2005, and as soon as I entered the value into the float field, it changed automatically to scientific notation.  I don't know if it's because of the version difference or because in the other system I was testing, the values were already entered.  Do you know?  The way the SQL Server 2005 acted is the way we would prefer for this project but, if not, I will just have to store as a decimal and convert as you explained.  Thanks for the info.
Alexis
0
 
LVL 14

Expert Comment

by:NBSO_ISS
ID: 20357383
Just note, if you convert to varchar, you would have to do code to convert back to the numeric format if changes are made to the data.  If you are just viewing, the conversion will work great.
0
 

Author Comment

by:alexisbr
ID: 20357698
Thanks.  The conversion is for view only.  Once the data is loaded into the database via DTS, no updates will ever be done to the data.  The data is then downloaded and manipulated by users for calculations and graphs/charts but on their PC's only.
Alexis
0
 

Author Comment

by:alexisbr
ID: 20400447
Thanks for your help.  SQL Server 2000 doesn't do the float as nicely as 2005 so I may just leave them as varchar and convert for the displays.

Regards,
Alexis
0

Featured Post

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
SQL Update trigger 5 16
Why i am getting a star, SSMS does not show me any error. Division Error 5 20
TSQL query to generate xml 4 31
error in my cursor 5 18
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now