locked
Error converting data type varchar to numeric. RRS feed

  • Question

  • Hi experts, I get:

    Msg 8114, Level 16, State 5, Line 6
    Error converting data type varchar to numeric.

    How can I find the row that its causing it?!  I did Isnumeric(column)=0, but no row is returned, so I do not understnad why  it is failing

    ps: I am using sql server 2016.

    Monday, April 23, 2018 9:15 AM

All replies

  • You can use TRY_CONVERT (Transact-SQL) to get them

    SELECT *
    FROM yourTable
    WHERE TRY_CONVERT(float, yourColumn) IS NULL


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Monday, April 23, 2018 9:18 AM
  • Perhaps you have data like in this example?

    SELECT CAST('12.5' AS INT)
    Msg 245, Level 16, State 1, Line 1
    Conversion failed when converting the varchar value '12.5' to data type int.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 23, 2018 9:22 AM
    Answerer
  • Check for cases where're doing explicit cast. Also check cases where you involve operations like addition, etc where columns with multiple datatypes are involved. Third thing to check if places where destination column has a different datatype than the source column

    If you can post some code where you believe this is happening then we might be able to suggest more


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 23, 2018 9:27 AM
  • You can use TRY_CONVERT (Transact-SQL) to get them

    SELECT *
    FROM yourTable
    WHERE TRY_CONVERT(float, yourColumn) IS NULL


    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    returns nothing! I do not understand! how is it possible??
    Monday, April 23, 2018 9:27 AM
  • Hi ,

    Can you right click one of the values and paste it here as example ? maybe some characters are hiding ..


    Regards, David .

    Monday, April 23, 2018 9:32 AM
  • Hi ,

    Can you right click one of the values and paste it here as example ? maybe some characters are hiding ..


    Regards, David .


    10761172
    Monday, April 23, 2018 9:37 AM
  • I even have a NOT LIKE '%[^0-9]%'       for that column... Not sure how it can still fail?!
    Monday, April 23, 2018 9:40 AM
  • No error

    SELECT CAST('10761172' AS INT)


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 23, 2018 9:40 AM
    Answerer
  • The issue is clearly in the join, I am joining an varchar(50) column to a decimal(18,0).

     

    My question is, I am even doing:

     

       AND column NOT LIKE '%[^0-9]%'  

     

     

    How can it still fail?

    Monday, April 23, 2018 9:50 AM
  • I even have a NOT LIKE '%[^0-9]%'       for that column... Not sure how it can still fail?!

    As suggested before

    You've to give us a little more info atleast

    Which statement is causing this?

    INSERT..SELECT

    or

    any operations like colA + colB - colC etc

    or CAST(colA AS int) etc?


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 23, 2018 9:51 AM
  • its just a select from. Now insert not cast, no nothing!

    select count(*)

    FROM   table1
           JOIN table2 
           ON varchar(50)  =decimal(18,0)

    where varchar(50) NOT LIKE '%[^0-9]%'    

    and it fails with : Error converting data type varchar to numeric.

    Monday, April 23, 2018 9:56 AM
  • The issue is clearly in the join, I am joining an varchar(50) column to a decimal(18,0).

     

    My question is, I am even doing:

     

       AND column NOT LIKE '%[^0-9]%'  

     

     

    How can it still fail?

    It can still fail depending on if the actual column contains non numerical data, even if it is not involved in the join

    Can you try like this and see if it works

    select count(*)
    
    FROM   (
    select *
    from table1
    where column NOT LIKE '%[^0-9]%'
    )t1
           JOIN table2  t2 
           ON varcharcolumn  =decimalcolumn



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 23, 2018 10:11 AM
  • The issue is clearly in the join, I am joining an varchar(50) column to a decimal(18,0).

     

    My question is, I am even doing:

     

       AND column NOT LIKE '%[^0-9]%'  

     

     

    How can it still fail?

    It can still fail depending on if the actual column contains non numerical data, even if it is not involved in the join

    Can you try like this and see if it works

    select count(*)
    
    FROM   (
    select *
    from table1
    where column NOT LIKE '%[^0-9]%'
    )t1
           JOIN table2  t2 
           ON varcharcolumn  =decimalcolumn



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Still fails.

    Even if I use WHERE TRY_CONVERT(numeric, yourColumn) IS NULL  returns nothing, but then it fails.

    Monday, April 23, 2018 10:14 AM
  • The issue is clearly in the join, I am joining an varchar(50) column to a decimal(18,0).

     

    My question is, I am even doing:

     

       AND column NOT LIKE '%[^0-9]%'  

     

     

    How can it still fail?

    It can still fail depending on if the actual column contains non numerical data, even if it is not involved in the join

    Can you try like this and see if it works

    select count(*)
    
    FROM   (
    select *
    from table1
    where column NOT LIKE '%[^0-9]%'
    )t1
           JOIN table2  t2 
           ON varcharcolumn  =decimalcolumn



    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Still fails.

    Even if I use WHERE TRY_CONVERT(numeric, yourColumn) IS NULL  returns nothing, but then it fails.

    Can you put together sample tables with similar data for us to reproduce the issue here?

    Then we can try debugging and suggesting a solution

    Mean while try this too

    IF OBJECT_ID('tempdb..#temp') IS NOT NULL DROP TABLE #temp
    
    select other columns,
    cast(column as numeric(30,4)) AS column
    INTO #temp
    from table1
    where column NOT LIKE '%[^0-9]%'
    
    select count(*)
    
    FROM   #temp t1
           JOIN table2  t2 
           ON t1.column = t2.decimalcolumn


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 23, 2018 10:45 AM
  • The issue is clearly in the join, I am joining an varchar(50) column to a

    decimal(18,0).

    So change the join to read:

    try_convert(numeric(18, 0), varcharcol) = numericcol

    If it still blows up, you are looking at the wrong place in the query.

    Monday, April 23, 2018 10:02 PM