none
Is there a way to selectively format fields in a column? some are scientific notation RRS feed

  • Question

  • Hello:

    I have a need to create a table in SQL server (13.0.5026)

    Once the table is created, I am attempting to set up an automated import of CSV files.

    My problem is this:

    Once of the fields stores some entries in scientific notation format

    (I am tracking microseconds)

    Is it possible to have SQL convert the entries that are in scientific notation to the same format?

    Or is there a setting in SQL that will allow it to accept this format along with the other?

    Please advise what options SQL has to address this.

    If there is nothing easy is it better to manipulate the format before hand with something like python?


    Friday, September 20, 2019 12:26 PM

Answers

  • Once of the fields stores some entries in scientific notation format

    Nope. SQL Server don't use "scientific notation format" for anything. SQL servers stores all data for a certain data type in a specific format.

    You don't mention what data type you have, but "scientific notation format" sounds like float, and float values are stored in the usual representation for float values, that is 53 bits of mantissa and an exponeent of 11 bits, and then a sign bit for each of them.
    You are probably talking about the text represenation of the values, which is something different. Floats are tricky, because they are exact values in the base-2 system, but that does not always translate well to base 10.

    How floats displays depends on the tool you are using. For instance, SSMS and SQLCMD may show the same float value differently. Since we don't know how your export these values, we cannot say what you can do about this.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 20, 2019 9:35 PM
    Moderator

All replies

  • Once of the fields stores some entries in scientific notation format

    Nope. SQL Server don't use "scientific notation format" for anything. SQL servers stores all data for a certain data type in a specific format.

    You don't mention what data type you have, but "scientific notation format" sounds like float, and float values are stored in the usual representation for float values, that is 53 bits of mantissa and an exponeent of 11 bits, and then a sign bit for each of them.
    You are probably talking about the text represenation of the values, which is something different. Floats are tricky, because they are exact values in the base-2 system, but that does not always translate well to base 10.

    How floats displays depends on the tool you are using. For instance, SSMS and SQLCMD may show the same float value differently. Since we don't know how your export these values, we cannot say what you can do about this.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, September 20, 2019 9:35 PM
    Moderator
  • Hi runatyr,

     

    >>Is it possible to have SQL convert the entries that are in scientific notation to the same format?

    Or is there a setting in SQL that will allow it to accept this format along with the other?

     

    As mentioned by Erland, there is no scientific notation format in sql server. You can store it by varchar format. Then you can test the most accurate conversion accuracy based on the value of varchar. Here is the same issue for your reference:https://stackoverflow.com/questions/7473081/convert-scientific-notation-to-float-when-using-openrowset-to-import-a-csv-file in the issue,  the most accurate conversion accurac was FLOAT then DECIMAL(24,12).

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 23, 2019 8:03 AM
  • Thank you to everyone for their help. Yes the text representation had some values listed with exponent notation and other  without. 

    AS it turns out.. 

    I could do a manual import of a flat file and the column was auto detected as a float.

    It all worked out.

    Thank you ... 

    I hopefully have one more question before i get this completed.

    Thank you again to everyone for helping me along.

    Thursday, September 26, 2019 5:58 PM