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

Analyst_SQL 3,531 Reputation points
2021-11-30T08:28:37.377+00:00

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);
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,297 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,519 questions
{count} votes

Accepted answer
  1. Olaf Helper 39,026 Reputation points
    2021-11-30T08:38:44.723+00:00

    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.

    0 comments No comments

2 additional answers

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-11-30T08:35:15.257+00:00

    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".

    2 people found this answer helpful.

  2. Tom Phillips 17,711 Reputation points
    2021-11-30T14:45:47.72+00:00

    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
    
    1 person found this answer helpful.