Sql-server – FORMAT ‘G17’ vs ‘G’

sql serversql-server-2012

I have a question about this fiddle:

DECLARE @TestVal AS float = 8.88;
SELECT flt = @TestVal
    , xml = (SELECT Value = @TestVal FOR XML PATH(''), TYPE)
    , fmt17 = FORMAT(@TestVal, 'G17')
    , fmt = FORMAT(@TestVal, 'G')
    , cst = CAST(@TestVal AS nvarchar(50))
    , fmt17_roundtrip = CAST(FORMAT(@TestVal, 'G17') AS float)
    , fmt_roundtrip = CAST(FORMAT(@TestVal, 'G') AS float)
    , cst_roundtrip = CAST(CAST(@TestVal AS nvarchar(50)) AS float)
;

https://dbfiddle.uk?rdbms=sqlserver_2019&fiddle=0cf05f882eb24f53e9484f043af99446

I was having trouble with some XML going out by default in scientific notation which, while not incorrect or inaccurate, isn't terribly readable.

I was originally using FORMAT(floatcol, 'G17') because of the comments on this documentation page that:

Note that, when used with a Double value, the "G17" format specifier ensures that the original Double value successfully round-trips. This is because Double is an IEEE 754-2008-compliant double-precision (binary64) floating point number that gives up to 17 significant digits of precision. We recommend its use instead of the "R" format specifier, since in some cases "R" fails to successfully round-trip double-precision floating point values. The following example illustrates one such case.

Well, today I found that it seems to add some extra insignificant digits to the string. In this particular example, they all "round trip" fine, but the G17 format has an extra insignificant digit.

And despite the fact that it might not affect me on a round-trip, I really don't want to be sending this to another party with extra digits.

Right now I am leaning towards changing to FORMAT('G'), but am not sure of the implications of that. Currently the format string used in these XML exports is a configuration setting stored in my system, so it's easiest to continue to use FORMAT since it requires no code changes.

So before I go testing on a variety of other values, my question is what is the difference between G and G17 overall and what problems might I run into using G instead of G17?

(Yes, this value needs to be a float, not a decimal or integer or currency, and the domain of the float can vary by different measurements/contexts).

System does need to run on 2012 and up.

Best Answer

my question is what is the difference between G and G17 overall and what problems might I run into using G instead of G17?

Sorry don't know about that but you could try to do something with the XML. Not sure if this something you could use but here it is anyway.

When dealing with typed XML you will not get the scientific notation for your value, you will get it for other values. So use typed XML and assign the resulting XML to a XML variable bound to the schema using xs:double for double precision.

Here is a schema.

create xml schema collection SC_F as '  
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
    <xs:element name="F" type="xs:double">
    </xs:element>
</xs:schema>
';

And a query using the schema.

declare @TestVal float = 8.88;
declare @X xml(SC_F);
set @X = (select @TestVal as F for xml path(''), type);
select @X;

Result:

<F>8.88</F>

Another way to fix it with XML is to get the float value into XML and then use the XML as a value to the XML query. Now that sounds more complicated than it is, I hope.

declare @TestVal float = 8.88;
select cast('' as xml).query('sql:variable("@TestVal")');

Result

8.88

Here is a little bit of code showing the different output you get for different solutions. Depending on values you will eventually get the scientific notation for all methods.

declare @T table(F float not null);

insert into @T(F) values(8.88),(0),(0.0001),(0.00001),(0.000001),
  (0.0000001),(0.84551240822557006);

declare @X1 xml;
declare @X2 xml(SC_F);
declare @X3 xml;
declare @X4 xml;
declare @X5 xml;

set @X1 = (select F from @T for xml path(''));
set @X2 = (select F from @T for xml path(''));
set @X3 = (select cast('' as xml).query('sql:column("F")') as F from @T for xml path(''));
set @X4 = (select format(F, 'G') as F from @T for xml path(''));
set @X5 = (select format(F, 'G17') as F from @T for xml path(''));

select @X1 as ScienceAllTheWay, 
       @X2 as UsingASchema, 
       @X3 as XMLTrickery,
       @X4 as FormatG,
       @X5 as FormatG17;

Results:

ScienceAllTheWay

<F>8.880000000000001e+000</F>
<F>0.000000000000000e+000</F>
<F>1.000000000000000e-004</F>
<F>1.000000000000000e-005</F>
<F>1.000000000000000e-006</F>
<F>1.000000000000000e-007</F>
<F>8.455124082255701e-001</F>

UsingASchema

<F>8.88</F>
<F>0</F>
<F>0.0001</F>
<F>1E-05</F>
<F>1E-06</F>
<F>1E-07</F>
<F>0.84551240822557006</F>

XMLTrickery

<F>8.88</F>
<F>0.0E0</F>
<F>0.0001</F>
<F>0.00001</F>
<F>0.000001</F>
<F>1.0E-7</F>
<F>0.84551240822557</F>

FormatG

<F>8.88</F>
<F>0</F>
<F>0.0001</F>
<F>1E-05</F>
<F>1E-06</F>
<F>1E-07</F>
<F>0.84551240822557</F>

FormatG17

<F>8.8800000000000008</F>
<F>0</F>
<F>0.0001</F>
<F>1.0000000000000001E-05</F>
<F>9.9999999999999995E-07</F>
<F>9.9999999999999995E-08</F>
<F>0.84551240822557006</F>
Related Question