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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

NBSO_ISSCommented:
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
Patrick MatthewsCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Scott PletcherSenior DBACommented:
Use FLOAT or REAL for the datatype; SQL will format the output in scientific notation when applicable.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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 PletcherSenior 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

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.