Display float values in scientific notation

Question

Hi.

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?

Thanks

Answer 1

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.