none
Getting Error:Conversion failed when converting the varchar value 'NULL' to data type int.

    Question

  • Hi All,

    When I am using below query,i am unable to convert varchar datatype to int,i am getting the error as below:

    Conversion failed when converting the varchar value 'NULL' to data type int.

    The query is as follows:

    SELECT convert(int,[OrderDetailID])
          ,convert(int,[OrderID])
          ,convert(nvarchar(50),[Sku])
          ,convert(float(24),[Quantity])
          ,convert(int,[productid])
      FROM [dbo].[OrderDetails]

    Can anyone give me necessary idea to get rid off from this error.


    sudha

    Friday, April 05, 2013 11:34 AM

Answers


  • Hi

    This is because while inserting data into table some one have insert  'NULL'  ac char instead NULL
    ie NULL as  char value.

    Example

      CREATE TABLE [dbo].[Dept](
    	[Deptid] [nvarchar](50) NOT NULL,
    	[Deptname] [nvarchar](max) NULL,
    	[deptmanagerid] [nvarchar](50) NULL,
    	[deptloc] [nvarchar](200) NULL,
    	[deptadress] [nvarchar](50) NULL,
    	[deptwork] [nvarchar](max) NULL
    	)
    	
    insert into Dept values (15 , 'abc','abc', NULL ,'abc','abc' )
    insert into Dept values (14 , 'abc','abc','NULL','abc','abc' )
    
    select convert(int,[deptloc]) ,  * from dept where Deptid = 14 ---> will throw error
    select convert(int,[deptloc]) ,  * from dept where Deptid = 15 ---> successfull

    To avoid same update all 'NULL'   to NULL

    Thanks
    Saurabh Sinha
    http://saurabhsinhainblogs.blogspot.in/
    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Thanks Saurabh Sinha http://saurabhsinhainblogs.blogspot.in/ Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Friday, April 05, 2013 11:53 AM
  • Looks like you character column contains character value of 'NULL' instead of NULL. This will be error when you try to convert the data. What you can do is to exclude the rows having 'NULL' during conversion or before the conversion update the value to NULL. Or if you are using SQL Server 2012 you can use the new TRY_CONVERT function.

    DECLARE @Test TABLE
    (
    CharCol VARCHAR(10)
    )
    INSERT INTO @Test VALUES
    	('12'), ('1023'), ('NULL'), (NULL)
    SELECT * FROM @Test
    -- This will be error
    /*
    SELECT CONVERT(INT, CharCol)
    FROM @Test
    */
    -- This will work
    SELECT CONVERT(INT, CharCol)
    FROM @Test
    WHERE CharCol <> 'NULL'
    -- Works in SQL 2012
    SELECT TRY_CONVERT(INT, CharCol)
    FROM @Test


    Krishnakumar S

    Friday, April 05, 2013 11:57 AM

All replies


  • Hi

    This is because while inserting data into table some one have insert  'NULL'  ac char instead NULL
    ie NULL as  char value.

    Example

      CREATE TABLE [dbo].[Dept](
    	[Deptid] [nvarchar](50) NOT NULL,
    	[Deptname] [nvarchar](max) NULL,
    	[deptmanagerid] [nvarchar](50) NULL,
    	[deptloc] [nvarchar](200) NULL,
    	[deptadress] [nvarchar](50) NULL,
    	[deptwork] [nvarchar](max) NULL
    	)
    	
    insert into Dept values (15 , 'abc','abc', NULL ,'abc','abc' )
    insert into Dept values (14 , 'abc','abc','NULL','abc','abc' )
    
    select convert(int,[deptloc]) ,  * from dept where Deptid = 14 ---> will throw error
    select convert(int,[deptloc]) ,  * from dept where Deptid = 15 ---> successfull

    To avoid same update all 'NULL'   to NULL

    Thanks
    Saurabh Sinha
    http://saurabhsinhainblogs.blogspot.in/
    Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Thanks Saurabh Sinha http://saurabhsinhainblogs.blogspot.in/ Please click the Mark as answer button and vote as helpful if this reply solves your problem

    Friday, April 05, 2013 11:53 AM
  • Looks like you character column contains character value of 'NULL' instead of NULL. This will be error when you try to convert the data. What you can do is to exclude the rows having 'NULL' during conversion or before the conversion update the value to NULL. Or if you are using SQL Server 2012 you can use the new TRY_CONVERT function.

    DECLARE @Test TABLE
    (
    CharCol VARCHAR(10)
    )
    INSERT INTO @Test VALUES
    	('12'), ('1023'), ('NULL'), (NULL)
    SELECT * FROM @Test
    -- This will be error
    /*
    SELECT CONVERT(INT, CharCol)
    FROM @Test
    */
    -- This will work
    SELECT CONVERT(INT, CharCol)
    FROM @Test
    WHERE CharCol <> 'NULL'
    -- Works in SQL 2012
    SELECT TRY_CONVERT(INT, CharCol)
    FROM @Test


    Krishnakumar S

    Friday, April 05, 2013 11:57 AM