none
Convert nvarchar to integer in sql server.

    Question

  • hii guys

    I have build a database with columns as "Nvarchar". Data is being inserted by using ssis package to import from excel files to the database.

    My problem is that when I am executing a query in SQL SERVER 2005 to select from database, I need to convert the data from "Nvarchar" to integer. I have use The cast function, convert function and i am getting an error :

    "Conversion failed when converting the nvarchar value '10,15' to data type int."

    The different statements i have used are below:

    a)

    select 
          case 
              when isnumeric([cost]) = 1 then 
                      cast([cost] AS int)
              else
                      NULL
         end

    b)

    SELECT st([Cost] as INT)
            FROM [mytable]

    c)

    SELECT Convert(INT,[cost
            FROM [mytable]

    I really need help please!!!

    Monday, July 23, 2012 6:10 AM

Answers

  • Hello,

    It's not a good idea to store numeric as varchar and then trying to handle string as numeric again.

    Anyway, by default SQL Server accepts numeric in en-US format, means with a dot as decimal separator. So you have to replace the comma by a dot:

    DECLARE @num nvarchar(100);
    SET @num = '10,15';
    
    SELECT CONVERT(float, REPLACE(@num, ',', '.')) AS Res


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by vatsa_mitr Monday, July 23, 2012 7:04 AM
    • Marked as answer by Vayl1 Tuesday, July 24, 2012 4:50 AM
    Monday, July 23, 2012 6:34 AM

All replies

  • Hello,

    It's not a good idea to store numeric as varchar and then trying to handle string as numeric again.

    Anyway, by default SQL Server accepts numeric in en-US format, means with a dot as decimal separator. So you have to replace the comma by a dot:

    DECLARE @num nvarchar(100);
    SET @num = '10,15';
    
    SELECT CONVERT(float, REPLACE(@num, ',', '.')) AS Res


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing

    • Proposed as answer by vatsa_mitr Monday, July 23, 2012 7:04 AM
    • Marked as answer by Vayl1 Tuesday, July 24, 2012 4:50 AM
    Monday, July 23, 2012 6:34 AM
  • thanks olaf.. the solution works!!! :)
    Tuesday, July 24, 2012 4:51 AM