Solved

Handling Scientific Notation in SQL Server table

Posted on 2007-11-26
10
5,992 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Azure Functions is a solution for easily running small pieces of code, or "functions," in the cloud. This article shows how to create one of these functions to write directly to Azure Table Storage.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

749 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