CODEWITHSUNDEEP
sql-serverfor-xml
Bryan
Bryan

Reputation: 12190

How to get SQL Server FOR XML to format float efficiently

When I use FOR XML to fetch values of type float from my SQL database, it formats them in scientific format, like this:

<foo bar="0.00000000e+000"/>

I would prefer it to say "0".

How can I persuade it to do this?

Upvotes: 3

Views: 3890

Answers (3)

Kalju P&#228;rn
Kalju P&#228;rn

Reputation: 31

Use my function, when need to be compatible with Excel or some other system like SAS uses 12 digits presision, NB! it does ROUND as well

CREATE OR ALTER FUNCTION dbo.FormatAbacus( @p FLOAT(53)) 
RETURNS VARCHAR(40) 
AS 
BEGIN 
   DECLARE @dec INT = 10 -- may be as parameter in future
   DECLARE @SIGN  AS CHAR(1) = IIF( @p < 0 , '-', NULL);
   IF @p IS NULL RETURN NULL
   IF @p = 0 RETURN '0'
   SET @p = ABS( @p);
   DECLARE @exp      AS INT         = FLOOR(LOG10(@p)) ;
   DECLARE @power    AS FLOAT       = POWER( CONVERT(FLOAT,10.0), @dec - @exp - 1);
   DECLARE @mantis   AS VARCHAR(40) = CONVERT( NUMERIC, ROUND( @p * @power,0));
   DECLARE @ret      AS VARCHAR(40) = CASE
            WHEN 0 > @exp THEN CONCAT( LEFT( '0.00000000000000',1-@exp), @mantis)
            WHEN @exp >= @dec THEN CONCAT( @mantis,LEFT( '000000000000000',@exp-@dec+1))
            WHEN @exp = @dec - 1 THEN @mantis
            ELSE STUFF( @mantis, @exp+2, 0, '.')
         END;
   IF @ret LIKE '%.%' SET @ret = LEFT( @ret, LEN(RTRIM(REPLACE(@ret,'0',' '))))
   IF @ret LIKE '%.' SET @ret = REPLACE(@ret,'.','')
   RETURN CONCAT( @SIGN, @ret)
END

Run:

SELECT 
      dbo.FormatAbacus(0.001234567890123456)              AS '_0.001234567890123456'
   ,  dbo.FormatAbacus(  0.1234567890123456)              AS '_0.1234567890123456'  
   ,  dbo.FormatAbacus(   1.234567890123456)              AS '_1.234567890123456'   
   ,  dbo.FormatAbacus(   123456.7890123456)              AS '_123456.7890123456'   
   ,  dbo.FormatAbacus(   123456789012.3456)              AS '_123456789012.3456'   
   ,  dbo.FormatAbacus(   12345678901234.56)              AS '_12345678901234.56'   
   ,  dbo.FormatAbacus(   12345678901234560.)             AS '_12345678901234560.'  
   ,  dbo.FormatAbacus(   123456789012345600.)            AS '_123456789012345600.' 
   ,  dbo.FormatAbacus(NULL)                              AS '_NULL'                
   ,  dbo.FormatAbacus(0)                                 AS '_0'                
FOR XML PATH('Abacus_12_digits'),TYPE

Produses:

<Abacus_12_digits>
  <_0.001234567890123456>0.00123456789012     </_0.001234567890123456>
  <_0.1234567890123456>    0.123456789012     </_0.1234567890123456>
  <_1.234567890123456>      1.23456789012     </_1.234567890123456>
  <_123456.7890123456>      123456.789012     </_123456.7890123456>
  <_123456789012.3456>      123456789012      </_123456789012.3456>
  <_12345678901234.56>      12345678901200    </_12345678901234.56>
  <_12345678901234560.>     12345678901200000 </_12345678901234560.>
  <_123456789012345600.>    123456789012000000</_123456789012345600.>
  <!--                      NULL is missing    -->
  <_0>                      0                 </_0>
</Abacus_12_digits>

Upvotes: 0

l33t
l33t

Reputation: 19937

CAST is your enemy. It can significantly reduce performance. Read this.

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107716

CAST is your friend

declare @t table (bar float);    insert @t values (0);
select bar from @t for xml path('foo');

------------------------------------------------
| <foo><bar>0.000000000000000e+000</bar></foo> |


declare @t table (bar float);    insert @t values (0);
select CAST(bar as decimal(10,2)) bar from @t for xml path('foo');

--------------------------------------------
|               <foo><bar>0.00</bar></foo> |


declare @t table (bar float);    insert @t values (0);
select CAST(bar as bigint) bar from @t for xml path('foo');

--------------------------------------------
|                  <foo><bar>0</bar></foo> |

Upvotes: 5

Related Questions