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
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