locked
Error converting data type nvarchar to bigint. RRS feed

  • Question

  • Hi experts,

    I execute the next code:

    SELECT CAST(Order_Number AS bigint)
    FROM Orders 
    WHERE Order_Number IS NOT NULL
    AND ISNUMERIC(Order_Number) = 1
    AND Order_Number NOT LIKE '%-%'
    AND Order_Number NOT LIKE '%.%'

    And besides having all those checks, it falis with: 'Error converting data type nvarchar to bigint.'

    How can I spot the row that is breaking this? The table has 1,000,000 rows, so I can't look for it visually.

    Thanks in advance!!!

    Wednesday, April 20, 2016 1:57 PM

Answers

  • Using NOT LIKE '%[^0-9]%' should exclude any OrderNumber that has any character other than '0' through '9'.  Putting that in a CTE would certainly ensure that the NOT LIKE is true before trying the conversion, but it shouldn't be trying the conversion on a record that will not be selected.  (If I know that I'm not going to select the record, why would I try to do work on it???)

    I'm wondering, do you need to CONVERT(BIGINT, 2147483647)??  Might it be trying to convert the BIGINT into an INT to do the comparison??  (Just a WAG, but I've seen stranger things...)

    HTH,

    Carl

    Wednesday, April 20, 2016 11:25 PM

All replies

  • Select top 10 * from Orders order by Order_Number 

    and

    Select top 10 * from Orders order by Order_Number  desc

    Please mark as answer if this post helped you

    Wednesday, April 20, 2016 2:00 PM
  •  SELECT Order_Number, Try_parse(Order_Number AS bigint)
    FROM Orders 
    WHere Try_parse(Order_Number AS bigint) is null

    • Proposed as answer by JamesKJ Wednesday, April 20, 2016 2:35 PM
    Wednesday, April 20, 2016 2:21 PM
  • Thanks for both replies; something crazy is really happening, I spotted a value and corrected it, but now I want to use a where clause:

     

    WHERE cast(Order_Number as bigint)<=2147483647

     

    And it fails, and then I use the

     

    SELECT Order_Number, Try_parse(Order_Number AS bigint)

    FROM Orders

    WHere Try_parse(Order_Number AS bigint) is null

     

    And retrieves 0 rows!! is this some sort of bug?!

    Wednesday, April 20, 2016 2:43 PM
  • Interesting. BTW, I found this interesting blog post

    http://blog.dbandbi.com/try_cast-try_convert-try_pa-rse-compare-contrast/


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, April 20, 2016 3:51 PM
  • I do not understand I tried every try_... and returns nothing... then I do

    cast(Order_Number as bigint) <=2147483647 and it fails.

    I tried using every try_xxx to find the row that breaks this but it won't fail...

    Problem is that the table is in sql 2008 and I cant use try_cast, I can only use cast and it fails... any help?

    Wednesday, April 20, 2016 4:25 PM
  • Try

    select * from myTable where

    order_number like '%[^0-9]%' - finds any non-numeric value


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, April 20, 2016 4:32 PM
  • I am already doing that, my where clause is

     

    order_number like '%[^0-9]%'

    and ISNUMERIC(Order_Number) = 1

    and convert(bigint, Order_Number) <=2147483647

    and order_number IS NOT NULL

    AND order_number NOT LIKE '%-%'

    AND order_number  NOT LIKE '%.%'

    AND order_number not LIKE '%,%'

     

     

    Why is it failing?? It can’t fail, I don’t know what else to try…

     

    I even tried cast instead of convert and also errors

    Wednesday, April 20, 2016 4:41 PM
  • Look for currency symbols. They are numeric, but won't convert to INTs

    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Wednesday, April 20, 2016 4:46 PM
  • When you have the CONVERT and the the LIKE conditions in the same WHERE clause, the CONVERT would fail even though a row with a comma or a period should have been eliminated by one of the NOT LIKE clauses.  For example, see below:

    CREATE TABLE #tmp(id VARCHAR(32));
    INSERT INTO #tmp VALUES ('999,999');
    
    -- Fails
    select * from #tmp
    where 
    	convert(bigint, id) <=2147483647
    	and id not like '%,%';
    
    -- parses
    select id,
    	TRY_PARSE(id as bigint)
    from #tmp
    WHere
    	Try_parse(id AS bigint) is null;
    
    -- fails
    SELECT * FROM #tmp
    WHERE CAST(id AS BIGINT) <= 2147483647;
    
    drop table #tmp;
    So eliminate the non-numeric (commas etc.) in a subquery or CTE and then apply the CONVERT function to the results of that subquery.


    • Edited by JamesKJ Wednesday, April 20, 2016 4:53 PM
    • Proposed as answer by Naomi N Wednesday, April 20, 2016 5:06 PM
    Wednesday, April 20, 2016 4:52 PM
  • To add to my previous reply, you could also use TRY_PARSE instead of CONVERT in the WHERE clause like this.

    order_number like '%[^0-9]%'
    and ISNUMERIC(Order_Number) = 1
    -- instead of convert, use try_parse
    -- and convert(bigint, Order_Number) <=2147483647
    and TRY_PARSE(order_number as bigint) <=2147483647
    and order_number IS NOT NULL
    AND order_number NOT LIKE '%-%'
    AND order_number  NOT LIKE '%.%'
    AND order_number not LIKE '%,%'
    


    Wednesday, April 20, 2016 5:04 PM
  • Did you run the query exactly as I've written? Just a single WHERE condition to find bad rows, e.g.

    select * from Orders where Order_Number like '%[^0-9]%' - find rows where you have something else besides numbers in the column (space, comma, ., letter)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Proposed as answer by KEAARPEE Wednesday, April 20, 2016 8:15 PM
    Wednesday, April 20, 2016 5:08 PM
  • Please try below 
     SELECT 
     Order_Number 
     ,PATINDEX('%[^0-9]%',(CONVERT(varchar(50), Order_Number )) ) AS 'Position of Non-Numeric Character'
     ,SUBSTRING((CONVERT(varchar(50),Order_Number)),PATINDEX('%[^0-9]%',(CONVERT(varchar(50),Order_Number))),1) AS 'Non-Numeric Character'
    FROM dbo.Orders 
    WHERE PATINDEX('%[^0-9]%',(CONVERT(varchar(50), Order_Number )) ) > 0

    Wednesday, April 20, 2016 8:16 PM
  • You need something like:

    SELECT CASE WHEN isnumeric(Order_Number) = 1 THEN cast(Order_Number AS bigint) END
    FROM   tbl
    WHERE  isnumeric(Order_Number) = 1

    The WHERE condition is not sufficient to avoid the error, because SQL Server may perform the convert before the filter. CASE is the only guaranteed way to avoid the crash.

    You may need to apply the other expressions as well.

    This is a lot easier on SQL 2012 where you can use try_cast.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Wednesday, April 20, 2016 10:09 PM
  • Using NOT LIKE '%[^0-9]%' should exclude any OrderNumber that has any character other than '0' through '9'.  Putting that in a CTE would certainly ensure that the NOT LIKE is true before trying the conversion, but it shouldn't be trying the conversion on a record that will not be selected.  (If I know that I'm not going to select the record, why would I try to do work on it???)

    I'm wondering, do you need to CONVERT(BIGINT, 2147483647)??  Might it be trying to convert the BIGINT into an INT to do the comparison??  (Just a WAG, but I've seen stranger things...)

    HTH,

    Carl

    Wednesday, April 20, 2016 11:25 PM
  • It worked great, thanks, now I get: Msg 248, Level 16, State 1, Line 2 The conversion of the nvarchar value '3545427103' overflowed an int column. The statement has been terminated. Is there any in-line elegant/best practice solution?
    Thursday, April 21, 2016 2:04 PM
  • Sure, CAST to BIGINT instead.


    Don't forget to mark helpful posts, and answers. It helps others to find relevant posts to the same question. Enjoyed my post? Hit the up arrow (left)
    Really enjoyed it? See my profile!
    My Tech Net Articles.

    Thursday, April 21, 2016 2:15 PM
  • could you please let us know what you have tried(query) and what is working and what is the issue? so that it would help us to guider further.
    Thursday, April 21, 2016 3:56 PM
  • Thanks a lot. Try_Parse is converting nvarchar to bigint.

    • Edited by ArulMV Wednesday, August 14, 2019 7:19 AM
    Wednesday, August 14, 2019 7:18 AM