Sql query to convert nvarchar to int

问题:

I have to query for total amount of a column using an aggregate function. The column data type is NVARCHAR(MAX). How can I convert it to Integer?

I have tried this:

  SELECT SUM(CAST(amount AS INT)),
         branch 
    FROM tblproducts  
   WHERE id = 4
GROUP BY branch

...but I'm getting:

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

回答1:

3600.00 is not integer so CAST via float first

sum(CAST(CAST(amount AS float) AS INT))

Edit:

Why float?

• no idea of precision or scale across all rows: float is the lesser evil perhaps
• empty string will cast to zero for float, fails on decimal
• float accepts stuff like 5E-02, fails on decimal

回答2:

In addition to gbn's answer, you need to protect against non-numeric cases:

sum(CASE WHEN ISNUMERIC(Amount)=1 THEN CAST(CAST(amount AS float) AS INT)END ) 

回答3:

SELECT sum(Try_Parse(amount as Int Using 'en-US')), branch FROM tblproducts
WHERE id = 4 GROUP BY branch