CODEWITHSUNDEEP
sqlsql-servert-sql
Simhadri
Simhadri

Reputation: 931

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.

Upvotes: 17

Views: 95526

Answers (3)

Adin Ballew
Adin Ballew

Reputation: 1

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

Upvotes: 0

A-K
A-K

Reputation: 17080

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 )

Upvotes: 8

gbn
gbn

Reputation: 432230

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

Upvotes: 30

Related Questions