Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to numeric.

Question

I have column datatype in nvarchar(50) ,now i want to convert it into decimal(10,2) ,it is giving error

Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to numeric.

ALTER TABLE containerno
ALTER COLUMN Cl_amt decimal(10,4);

Answer 1

Error converting data type nvarchar to numeric.

A clear error message, your varchar data contains in some rows not convertable/numeric values.
You can try finding them with

select *
from containerno
where isnumeric(Cl_amt) = 0

But no garantuee it will find all cases, e.g. exponential presentation of floats like 10E2 are numerics to, but not implicit convertable to numeric data type, only to float.

Answer 2

You might need to revise the data in the column, but anyway you can do one of the following:-

1- check if it is numeric then convert it else put another value like 0

Select Cl_amt, CASE WHEN Isnumeric(Cl_amt) = 1
THEN CONVERT(DECIMAL(10,4),Cl_amt) 
ELSE 0 END AS Cl_amt2
FROM containerno

2- select only numeric values from the column

    SELECT Cl_amt ,CONVERT(DECIMAL(10,4),Cl_amt) AS Cl_amt
  FROM containerno
  WHERE Isnumeric(Cl_amt) = 1

Or:

UPDATE containerno
SET [Cl_amt]=CASE WHEN Isnumeric([Cl_amt]) = 1
                  THEN CONVERT(DECIMAL(10,4),[Cl_amt]) 
ELSE 0 END 
FROM containerno;

ALTER TABLE [Table 1]
ALTER COLUMN [File Name] decimal(10,4);

Regards,
Echo

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Answer 3

You have data in Cl_amt which is not decimal. Run this to find them.

select *
 from containerno
 where TRY_CAST(Cl_amt as decimal(10,4)) IS NULL AND Cl_amt IS NOT NULL