Handling Scientific Notation in SQL Server table

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
alexisbrAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Patrick MatthewsConnect With a Mentor Commented:
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
 
NBSO_ISSConnect With a Mentor Commented:
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
 
Scott PletcherSenior DBACommented:
Use FLOAT or REAL for the datatype; SQL will format the output in scientific notation when applicable.
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
alexisbrAuthor Commented:
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
 
alexisbrAuthor Commented:
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
 
Scott PletcherConnect With a Mentor Senior DBACommented:
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
 
alexisbrAuthor Commented:
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
 
NBSO_ISSCommented:
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
 
alexisbrAuthor Commented:
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
 
alexisbrAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.