SQL Server Conversion error: Conversion failed when converting the nvarchar value 'XXX' to data type int

2
votes

Running some pretty simple SQL here:

select * 
from table
where columnA <> convert(int,columnB)
  and isnumeric(columnB) = 1

Still getting this error every time:

Conversion failed when converting the nvarchar value 'XXX' to data type int.

5
sql-serversql-server-2008tsql
What are the data types of columnA and columnB? This error could be thrown if columnA contains 'XXX' - Nick.McDermaid
Does this question's answer help you? Perhaps putting your convert inside of a case statement would help. - Chris
One more thing, If you have a decimal in columnB it will pass isnumeric() but still fail on your convert to integer. You need extra measure to verify it is an integer. - Chris
Thanks @Chris both comments are spot on. - rhess21

5 Answers

1
votes

If you're using SQL Server 2012 or more recent you could use TRY_PARSE which will return NULL when the parse fails.

SELECT TRY_PARSE('one' as int)   -- NULL
     , TRY_PARSE('1' as int)     -- 1
     , TRY_PARSE('0.1' as int)   -- NULL

Returns the result of an expression, translated to the requested data type, or null if the cast fails in SQL Server. Use TRY_PARSE only for converting from string to date/time and number types.

0
votes

Isnumeric has a lot of odd behavior. For example, it also considers currency signs such as $ or £, and even a hyphen (-) to be numeric.

I think you'd be better of using 

NOT columnB like '%[^0-9]%'

Check the comments at the bottom of the msdn page for isnumeric(), which you can find here: https://msdn.microsoft.com/en-us/library/ms186272.aspx

0
votes

This may sound weird, but it breaks when do not put the ISNUMERIC check first. Try this out:

WITH [Table]
AS
(
    SELECT columnA,columnB
    FROM
    (
        VALUES  (1,'2'),
                (2,'XXX')
    ) A(columnA,columnB)
)

select *
from [Table]
where       ISNUMERIC(columnB) = 1          --this works
        AND columnA <> convert(int,columnB)

--where     columnA <> convert(int,columnB) --this doesn't work
--      and isnumeric(columnB) = 1
0
votes

I suggest you to reverse your checking like this:

SELECT * 
FROM table
WHERE CONVERT(NVARCHAR, columnA) <> columnB
0
votes

I got this using a combination of the answers and comments here. I used a CASE statement in my WHERE clause and also had to use LIKE instead of ISNUMERIC to account for illegal characters. I also had to use BIGINT because a few select samples were overflowing the INT column. Thanks for all of the suggestions everybody!

select * from patient
where PatientExternalID <> 
    (case when mrn not like '%[^0-9]%'
          then convert(bigint, mrn) 
       else 0 
    end)