I have a field to update which uses isnull(field,'') = 2. But I am getting error as the field has character value in it?

for exaample : field = 'E' It says conversion failed converting varchar value 'E' to datatype int

  • Are you asking why you are getting the error message or is your question more to do with the ISNULL statement? – Shaneis May 19 '16 at 15:24

Your problem is that you are working with two different data types. ISNULL(field,'') implies that you are working with a character field. 2 however is a number. Which do you need?

You can do something like this:

ISNULL(field,'') = '2'

But if field happens to be '02' then the result will be false.

Or if field is actually a number then you could do something like this:

ISNULL(field,0) = 2

But if field is a string data type that you are using to hold numbers and you just happen to have an 'Q' in the field then you are going to get a conversion error.

I'm assuming the first option is best since you are getting the error on 'E'.

When you use the code you actually have it is doing an implicit conversion behind the scenes. If you look at the XML execution plan for the following code:

CREATE TABLE #DataTypeTest (Col1 varchar(50))

INSERT INTO #DataTypeTest VALUES ('1'),('2')

FROM #DataTypeTest
WHERE ISNULL(Col1,'') = 2

You'll see this line (talking about the ISNULL)

 <ScalarOperator ScalarString="CONVERT_IMPLICIT(int,isnull([tempdb].[dbo].[#DataTypeTest].[Col1],''),0)=(2)">

You can see how it's actually converting the ISNULL in to an INT data type. This is because you are comparing it to a integer. It doesn't convert the 2 to a string because INT is higher than VARCHAR on the data type precedence list.

Now when it tries to convert the ISNULL formula into an integer it of course fails when the column contains an E.

| improve this answer | |
  • 2
    You should add that using ISNULL, even WHERE ISNULL(field,'') = '2' that won't throw error, is practically useless. WHERE field = '2' would be simpler and have the same effect. – ypercubeᵀᴹ May 19 '16 at 16:29
  • @ypercubeᵀᴹ Very true. I didn't think to include that. I was focused on the data type confusion. – Kenneth Fisher May 19 '16 at 16:33

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.