Conversion failed when converting the nvarchar value '3.218E7' to data type int.

Decompressor 81 Reputation points
2021-02-22T14:14:45.523+00:00
 declare @s nvarchar(max)='1. Some data - 32180000' declare @separator char(1)=space(1); SELECT cast('<root><r>'+replace(@s,@separator,'</r><r>') + '</r></root>' as xml).query('<root> { for $x in /root/r return if (xs:int($x) instance of xs:int) then $x else () (: filter out non-integers :) } </root>').query('sum(/root/r/text())').value('.','int') as result 
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,582 questions
0 comments No comments
{count} votes

Accepted answer
  1. Yitzhak Khabinsky 25,476 Reputation points
    2021-02-22T14:55:36.66+00:00

    @Decompressor ,

    Amazing, the sum() converts its calculation result to a scientific notation trying to make it more compact.

    Useful link: type-casting-rules-in-xquery

    Please try the following solution.

    SQL

    DECLARE @s NVARCHAR(MAX) = N'1. Some data - 32180000';  
      
    DECLARE @separator CHAR(1) = SPACE(1);  
      
    SELECT CAST('<root><r>' +   
     REPLACE(@s, @separator, '</r><r>') + '</r></root>' AS XML)  
     .query('<root>   
     {   
     for $x in /root/r return if (xs:int($x) instance of xs:int) then   
     $x else () (: filter out non-integers :)   
     }  
     </root>')  
    .query('sum(/root/r/text()) cast as xs:int?').value('.','INT') as result;  
    

    If you have really gigantic numbers you can handle them as follows:

    .query('sum(/root/r/text()) cast as xs:unsignedLong?').value('.','BIGINT') as result;  
    

    Output

    +----------+  
    |  result  |  
    +----------+  
    | 32180000 |  
    +----------+  
    
    0 comments No comments

0 additional answers

Sort by: Most helpful