Scientific number formatting in SQL Server-CodePudding

SELECT
    fld AS val, 
    CONVERT(VARCHAR, FORMAT(fld, 'E2')) AS _2decimal, 
    CONVERT(VARCHAR, FORMAT(fld, 'E3')) AS _3decimal

Result:

val	_2decimal	_3decimal
0.0000007018	7.02E-006	7.018E-006

The FORMAT function does the job fine with SQL Server (as of 2012).

I'm looking for the functions doing the same job but with older versions of SQL Server.

Thank you

CodePudding user response：

Using LEFT & RIGHT & ROUND it can generate that format without FORMAT.

SELECT fld
, CONCAT(ROUND(LEFT(CONVERT(VARCHAR(20),CONVERT(FLOAT,fld),1),9),2), UPPER(RIGHT(CONVERT(VARCHAR(20),CONVERT(FLOAT,fld),1),5))) AS _2decimal
, CONCAT(ROUND(LEFT(CONVERT(VARCHAR(20),CONVERT(FLOAT,fld),1),9),3), UPPER(RIGHT(CONVERT(VARCHAR(20),CONVERT(FLOAT,fld),1),5))) AS _3decimal
FROM (values
(1.2345E-6), 
(-1.2345E-6), 
(0.0000007018)
) q(fld)

fld _2decimal _3decimal

1.2345E-06 1.23E-006 1.234E-006

-1.2345E-06 -1.23E-006 -1.234E-006

7.018E-07 7.02E-007 7.018E-007

fld	_2decimal	_3decimal
1.2345E-06	1.23E-006	1.234E-006
-1.2345E-06	-1.23E-006	-1.234E-006
7.018E-07	7.02E-007	7.018E-007

Test on db<>fiddle here

CodePudding user response：

I think I got the round-up-to-10 issue fixed. Will come back and update later: https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=624024aa6633145ed1d0c032e123bccb

declare @places int = 3;
declare @val float = -1.255e-127;

select
stuff(
    convert(varchar(24), @val, 2), 1, 17   case when sign(@val) = -1 then 1 else 0 end,
    left(
        cast(round(cast(
            left(
                convert(varchar(24), @val, 2),
                @places   3   case when sign(@val) = -1 then 1 else 0 end
            )
            as decimal(17, 16)),
            @places) as varchar(20)),
        @places   2   case when sign(@val) = -1 then 1 else 0 end
    )
)

This is all basically inline. The assumptions are that convert returns a value with exactly 15 decimal places (which is documented as "always 16" counting the lead digit.) Since it's scientific notation there will be a leading digit and decimal place which is where the 2 comes into play. If the value is negative then the string is one character longer.

Fourteen places is the maximum because of the rounding portion. If you need 15 places then just branch grab the output of convert(varchar, f, 2).

To handle zero decimal places and then drop the decimal point you might want to wrap the whole thing up with replace(<expression>, '.', replicate('.', sign(@places)))

https://dbfiddle.uk/?rdbms=sqlserver_2014&fiddle=92fcf5b6c152eda9072900de1d28cbb1