Solved

Handling Scientific Notation in SQL Server table

Posted on 2007-11-26
10
6,080 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

717 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