none
Cannot convert nvarchar to integer/numeric/decimal

    질문

  • 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!!!

    2012년 7월 23일 월요일 오전 8:53

답변

  • The problem with isnumeric is that it returns 1, if the value can be converted to any of the numeric data types, and indeed:

       SELECT convert(money, '10,15')

    returns 1015.

    To test for conversion to int use, this instead:

       CASE WHEN cost NOT LIKE '%[^0-9]%' THEN cast(cost AS int) END

    Note that this is not foolproof. It will not convert negative numbers, and it will bomb if there is a value that exceeds the range for an int.

    In SQL 2012 there is try_convert/try_cast which makes this so much easier.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012년 7월 23일 월요일 오전 9:05

모든 응답

  • Atleast one of the rows will have a value so "'10,15'". In that case SQL server would not be able to convert it to integer.

    Workarounds:

    1. split such string and then convert into integer

    2. You can omit those rows while inserting.

    2012년 7월 23일 월요일 오전 8:58
  • The problem with isnumeric is that it returns 1, if the value can be converted to any of the numeric data types, and indeed:

       SELECT convert(money, '10,15')

    returns 1015.

    To test for conversion to int use, this instead:

       CASE WHEN cost NOT LIKE '%[^0-9]%' THEN cast(cost AS int) END

    Note that this is not foolproof. It will not convert negative numbers, and it will bomb if there is a value that exceeds the range for an int.

    In SQL 2012 there is try_convert/try_cast which makes this so much easier.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012년 7월 23일 월요일 오전 9:05
  • HI Vay
    DECLARE    @VAL NVARCHAR(MAX)
    SELECT    @VAL = '10,50'
    SELECT    PATINDEX('%[^0-9]%', @Val)
    
    SELECT    CASE    WHEN ISNUMERIC(@VAL) = 1    AND PATINDEX('%[^0-9]%', @Val) < 0
                    THEN CAST(@VAL AS INT)
                    END

    !

    You might get the desired output using below query;



    Please let me know if this doesn’t work for you. Hope I have answered you correctly.

    Thanks, Hasham
    2012년 7월 23일 월요일 오전 9:09
  • Can you try with the below link to use the function, if you are not in SQL Server 2012 Version as Erland suggested?

    http://www.sql-server-performance.com/forum/threads/enhanced-isnumeric-function.20618/

    Usage:

    Create Table T1 (Col1 Varchar(50))
    Insert into T1 Select '001'
    Insert into T1 Select '101'
    Insert into T1 Select '201'
    Insert into T1 Select '1001'
    Insert into T1 Select '1001,10'
    Select Cast(Case When dbo.Is_numeric(Col1) = 1 then Col1 Else Null End as int) From T1 

    2012년 7월 23일 월요일 오전 9:14
  • Try to use ROUND function before conferting

    SELECT CONVERT(int,ROUND(MyValue,0)) FROM MyTable

    2012년 7월 23일 월요일 오전 9:17
  • you can use try, catch block while importing data. if catch then return 0, else the same number. maybe you can use some function for this:

    SELECT (select NumeciCheck([Cost]))  FROM [mytable]

    CREATE FUNCTION NumericCheck
    (
    @NumberString NVARCHAR(100)
    )
    RETURNS INT
    AS
    BEGIN
    DECLARE @ReturnValue INT

    BEGIN TRY
    SET @ReturnValue = CONVERT(INT, @NumberString)
    END TRY
    BEGIN CATCH
    SET @ReturnValue = 0 -- OR NULL
    END CATCH

    RETURN @ReturnValue
    END
    GO

    regards

    joon

    2012년 7월 23일 월요일 오전 9:31
  • Thanks for your help.. it works.. :)
    2012년 7월 24일 화요일 오전 4:52
  • This also work... but the SELECT convert(money, '10,15') is much easier to use in my case.. thanks.. :)

    2012년 7월 24일 화요일 오전 4:54
  • This also work... but the SELECT convert(money, '10,15') is much easier to use in my case.. thanks.. :)

    Wait! If you think that 10,15 is the same as 1015, I guess it is alright. But are you sure that 10,15 really means that and not for instance 10.15? Just because you don't get an error does not mean that it works!


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    2012년 7월 24일 화요일 오전 8:03