Display float values in scientific notation

Padmanabhan, Venkatesh 241 Reputation points


I have a float column which contains data in the format of 0.73, 0.0

I want to display the float values in the format of scientific notation . example: +4.50000000000000E-001

I have tried this, but does not seem correct and not able to get the + and - symbols at the start.

format(COMMISSION, '0.000000000000000E+0') as COMMISSION

How to fix this in the requested format?


SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,653 questions
0 comments No comments
{count} votes

1 answer

Sort by: Most helpful
  1. Dan Guzman 9,206 Reputation points

    Custom numeric format strings allow conditional formatting depending on whether the value is positive, negative, or zero. You can specify two different format strings with the desired sign literal separated with a semi-colon so that the first format string is applied to positive/zero values and the second for negative values.

    For example:

    FORMAT(COMMISSION, '+0.000000000000000E+0;-0.000000000000000E+0') AS COMMISSION  

    Also be aware that float columns are stored in a binary structure in the database have no "format" as suggested by your question. It's generally best to format data for display purposes in app code but one can use T-SQL for the task by converting the value to a string like the above example or similarly using CAST/CONVERT.

    1 person found this answer helpful.
    0 comments No comments